Method 'Range' of object '_Worksheet' failed - Naming range and referring to it in VBA

Question

Within a single macro, I am trying to select a range, name it, and then refer to that range when AutoFiltering data. Here's my code:

'Select and name range
Set bDataDump = ActiveWorkbook
Set DataDump = bDataDump.Sheets(1)    
DataDump.Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFilter
Range(Selection, Selection.End(xlDown)).Select
Set myrange = Selection

'AutoFilter that range for everything but PDC1 and delete visible rows
    'Code fails on next line
DataDump.Range("myrange").AutoFilter field:=11, Criteria1:="<>""PDC1"""
ActiveCell.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
DataDump.ShowAllData

I get the "Method 'Range' of object '_Worksheet' failed" when I run the macro, and the debugger highlights the first line of the second block of code as the culprit. I have not been able to find any examples on the web of filtering data like this, so I would appreciate any insight as to what I'm doing wrong. Thanks!

lol@pnuts: You make me look like a devil :) :@SMPerron [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Oct 15 '13 at 21:02

score 3 · Answer 1 · answered Oct 15 '13 at 20:51

3

DataDump.Range("myrange").AutoFilter field:=11, Criteria1:="<>""PDC1"""

should be

myrange.AutoFilter field:=11, Criteria1:="<>""PDC1"""

answered Oct 15 '13 at 20:51

Tim Williams

154,628
8
97
125

score 3 · Accepted Answer · answered Oct 15 '13 at 20:54

3

myrange is a Range and not a String. Therefore replace:

DataDump.Range("myrange").AutoFilter field:=11, Criteria1:="<>""PDC1"""

with

myrange.AutoFilter field:=11, Criteria1:="<>""PDC1"""

answered Oct 15 '13 at 20:54

Gary's Student

95,722
10
59
99

+ 1 for explaining the issue :) – Siddharth Rout Oct 15 '13 at 21:04
Makes perfect sense now. Thanks! – Kes Perron Oct 16 '13 at 18:35

Kes Perron · Answer 3 · 2013-10-16T19:53:32.650

For posterity, I present to you my updated code. It includes the replacement of all .Select statements.

Set bDataDump = ActiveWorkbook
Set DataDump = bDataDump.Sheets(1)
Set myrange = DataDump.Range("A1")
Set myrange = Range(myrange, myrange.End(xlToRight))
Set myrange = Range(myrange, myrange.End(xlDown))
'
myrange.AutoFilter field:=12, Criteria1:="<>PDC1"
Set dltrng = DataDump.Range("A2")
Set dltrng = rnage(dltrng, dltrng.End(xlDown))
dltrng.EntireRow.Delete
DataDump.ShowAllData

I find it very strange that the filter part of the recorded macro required doubled quotes

ActiveSheet.Range("$A$1: ... Criteria1:="<>""PDC1"""

but required single quotes when starting with a named range (see line 7 above).

Method 'Range' of object '_Worksheet' failed - Naming range and referring to it in VBA

3 Answers3

Linked