Why is this code saying Method Range of Worksheet failed

[Darren Smith]

This code says "Method Range of Worksheet failed" on this line

 colorAbove ws.Range("A13:Q & LastRow").

Can`t understand why???

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 "Break Lines 2 Page Job Card"
    cmb.AddItem "Break Lines 3 Page Job Card"
    cmb.AddItem "Break Lines 4 Page Job Card"
    cmb.AddItem "Break Lines 5 Page Job Card"
    
    LastRow = ws.Cells(Rows.Count, 3).End(xlUp).row
    MsgBox LastRow

    
    Select Case cmb.Value
    
          Case ("Break Lines 1 Page Job Card")
                        colorAbove ws.Range("A13:Q & LastRow")
            
          Case ("Break Lines 2 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q & LastRow")
                        
          Case ("Break Lines 3 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q & LastRow")
                        
           Case ("Break Lines 4 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q183")
                        colorAbove ws.Range("A188:Q & LastRow")
                        
           Case ("Break Lines 5 Page Job Card")
                        colorAbove ws.Range("A13:Q61")
                        colorAbove ws.Range("A66:Q122")
                        colorAbove ws.Range("A127:Q183")
                        colorAbove ws.Range("A188:Q244")
                        colorAbove ws.Range("A249:Q & LastRow")
    End Select

End Sub
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

 

[sykes]

NB untested; test on a copy of your work, first.

Try:

colorAbove ws.Range("A13:Q" & LastRow)

... and duplicate for all the other instances, of course.

 

[mohadin]

colorAbove is a sub so
call colorAbove (ws.Range("A66:Q" & LastRow))

Or You make it as function then
colorAbove (ws.Range("A66:Q" & LastRow))

 

[Darren Smith]

Thanks for your help all works fine now

 

[mohadin]

Cause we can not test so witch one was the solution for you? If you please

 

[RoryA]

Whether it is a sub or function does not affect how you call it. For either one, you could use Call with parentheses, or don't use Call and don't use parentheses.

 

[sykes]

Pleasure, and thanks for the feedback.

 

[mohadin]

@RoryA Thanks a lot