error 1004 method range of object _worksheet failed

martipe1

Board Regular
Joined
Mar 16, 2007
Messages
71
I get the above error message, this is my code:

Sub Filters()

Dim WorkWks As Worksheet
Dim rngA As Range

Set WorkWks = Worksheets("WorkInProcess")
Set rngA = WorkWks.Range("Dates")

The error is in the Set rngA line

The named range is =OFFSET(WorkInProgress!$T$1,0,0,COUNTA(WorkInProcess!$T:$T),1)

The error only occurs when Column has 0-1 Rows, I assume when rows = 0 is due to #Ref error

How can I check if there is an error in the range and can exit the sub?

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try using On Error, as follows:
VBA Code:
Set WorkWks = Worksheets("WorkInProcess")
On Error Resume Next
    Set rngA = WorkWks.Range("Dates")
On Error GoTo 0
If rngA Is Nothing Then
    MsgBox ("Invalid Range, aborted")     'delete this line if msgbox unwanted
    Exit Sub
End If
'your macro continue
Bye

(wfpbs)
 
Upvote 0
Solution
Anthony beat me to the punch.
I think that should solve it but in case anyone else buys into this I wasted quite a bit of time because I did not initially pick up the inconsistencies in the original data provided.

Code
Set WorkWks = Worksheets("WorkInProcess")
Set rngA = WorkWks.Range("Dates")

named range is
=OFFSET(WorkInProgress!$T$1,0,0,COUNTA(WorkInProcess!$T:$T),1)
 
Upvote 0
Try using On Error, as follows:
VBA Code:
Set WorkWks = Worksheets("WorkInProcess")
On Error Resume Next
    Set rngA = WorkWks.Range("Dates")
On Error GoTo 0
If rngA Is Nothing Then
    MsgBox ("Invalid Range, aborted")     'delete this line if msgbox unwanted
    Exit Sub
End If
'your macro continue
Bye

(wfpbs)
Thank you very much, just what I needed
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top