1

I've added the last row to select case code see below. The error says

Method Range of Object _Worksheet Failed

Not too sure how to deal with the error? Does it mean it cant find the range on the worksheet? Ive put the color procedure below for you to see The code works fine except for the Last Row in the ranges

Option Explicit

Private Sub Add_Break_Lines_Click()

    Dim Com As ComboBox
    Dim ws As Worksheet

    Set ws = Application.Workbooks("Automated ardworker.xlsm").Worksheets("Job Card Master")
    Set Com = Me.Add_Break_Lines
    LastRow = ws.Range("C299").End(xlUp).row
    
    With ws
        Select Case Com.Value
        Case "Break Lines 1 Page Job Card"
            Color .Range("A13:Q & LastRow")
        End Select
    End With

End Sub

Function Color(rng As Range)

    Dim row As Range
    Dim EmptyRowNum As Integer
    
    For i = 1 To rng.Rows.Count
        Set row = rng.Rows(i)
        If WorksheetFunction.CountA(row) = 0 Then
            EmptyRowNum = EmptyRowNum + 1
        End If
        If EmptyRowNum = 2 Then
            EmptyRowNum = 0
            row.Interior.ColorIndex = 6
        End If
    Next i

End Function
Donald Duck
  • 29
  • 4
  • 1
    `Color .Range("A13: & LastRow")` doesn't make much sense. What are you trying to do? The range needs to be something like `"A13:A" & LastRow`. – SJR Jan 29 '21 at 14:35
  • Could you post the `Color` procedure? – VBasic2008 Jan 29 '21 at 14:41
  • Also check this `Automated ardworker.xlsm`, it may have been misspelled. Looks like it could be `CardWorker`. – VBasic2008 Jan 29 '21 at 14:48
  • Sorry it does now say Cardworker but still not working – Donald Duck Jan 29 '21 at 14:54
  • I`ve added the Color Procedure to the code above – Donald Duck Jan 29 '21 at 14:55
  • You still have `"A13: & LastRow"` which is not right - Try `"A13:A" & LastRow` at least – braX Jan 29 '21 at 15:00
  • Sorry I have tried the above still no luck – Donald Duck Jan 29 '21 at 15:07
  • The `Function` is not a function it is a `Sub`, because it is not returning anything, it is just 'doing'. Although not necessary, using `ByRef rng As Range` may indicate that you are about to do something to the range `Integer` might fail, rather use `Long`. `row` is an ugly name for a range variable although it is referring to a row range. Rather use something like `rowRng, rRng, rrg...`. Could you explain what the function is supposed to do? What if there are three consecutive empty rows? – VBasic2008 Jan 29 '21 at 15:11
  • The idea is to fill the row with color above the rows with values filled in. – Donald Duck Jan 29 '21 at 15:30
  • All your advice makes sense I have changed mine to suit. There is always 2 rows between cause it`s a template – Donald Duck Jan 29 '21 at 15:36
  • What is the name of the workbook where this codes are located? What is the name of the worksheet where the event code is located? Is it in the same workbook? Where is the function located (module name)? I don't know how to trigger the `Click` event, but with a combo box mostly the `Change` event is used. – VBasic2008 Jan 29 '21 at 15:51
  • Workbook Name is Automated Job card Worksheet is Job Card Master. The event code is in the same workbook. I changed Click to Change. The function is in the form coding the same as the rest of the code – Donald Duck Jan 29 '21 at 16:01
  • Hi again. Your logic to detect empty rows fall apart because you have a formula in column `P` which returns `""` if column `H` is empty. `WorksheetFunction.CountA()` does NOT consider a `""` entry in a cell as empty (as I wrote to you in a comment). I suggested, to NOT include columns `O, P and Q` in your test. Exclude column `P` as a minimum. Thus you should write `Color .Range("A13:N & LastRow")` to test only columns `A` to `N`. If you want you can still paint the columns `O, P and Q` if you like. Alternatively, reduce the range just before the call to `WorksheetFunction.CountA()` – Tom Brunberg Jan 29 '21 at 16:07

1 Answers1

1

Combo Box Change

  • Always use Option Explicit.

  • Use variable names that are different than the names of keywords, properties, methods ... that are used in VBA.

  • You may consider using

    If WorksheetFunction.CountBlank(rrg) = rrg.Columns.Count Then

    in the function for the reason Tom Brunberg indicated in the comments, namely if you have formulas evaluating to "" in the range, there will never be an empty row, just blank ones. Maybe use a variable before the loop:

    cCount = rng.Columns.Count

Sheet Module e.g. Sheet1 (In VBA the name in parentheses)

Option Explicit

Private Sub Add_Break_Lines_Change()

    Dim cmb As ComboBox
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = ThisWorkbook.Worksheets("Job Card Master")
    Set cmb = Me.Add_Break_Lines
    cmb.AddItem "Break Lines 1 Page Job Card"
    cmb.AddItem "Dummy"
    LastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).row
    
    Select Case cmb.Value
        Case "Break Lines 1 Page Job Card"
            colorAbove ws.Range("A13:Q" & LastRow)
    End Select

End Sub

Standard Module e.g. Module1 (Optionally in the same sheet module)

Option Explicit

Sub colorAbove(rng As Range)
    
    Dim brg As Range
    Dim rrg As Range
    Dim EmptyRowNum As Long
    Dim i As Long
    
    For i = 1 To rng.Rows.Count
        Set rrg = rng.Rows(i)
        If WorksheetFunction.CountA(rrg) = 0 Then
            EmptyRowNum = EmptyRowNum + 1
        End If
        If EmptyRowNum = 2 Then
            EmptyRowNum = 0
            If brg Is Nothing Then
                Set brg = rrg
            Else
                Set brg = Union(brg, rrg)
            End If
        End If
    Next i
    
    If Not brg Is Nothing Then
        brg.Interior.ColorIndex = 6
    End If

End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28