Why is this code saying Method Range of Worksheet failed

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
417
Office Version
  1. 2019
Platform
  1. Windows
This code says "Method Range of Worksheet failed" on this line
VBA Code:
 colorAbove ws.Range("A13:Q & LastRow").

Can`t understand why???

VBA Code:
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
NB untested; test on a copy of your work, first.

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

... and duplicate for all the other instances, of course.
 
Last edited:

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
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))
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,719
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Solution

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Pleasure, and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,940
Messages
5,639,097
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top