For each with named Ranges

Question

I'm having some trouble here and I'm not sure exactly what's going on. I've got the below code;

Set wks = Worksheets.Add
    wks.Range("A1").ListNames
Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Name = "GroupList1"

For Each a In Range("GroupList1")
    Range(a).Select
    If Selection.Find("NOTE:", , Excel.xlValues) Is Nothing Then
        Range(a).EntireRow.Delete
    End If
Next a

I keep getting an error "Method 'Range' of object'_Global' failed" when trying to select each Range.

Range(a).Select

The weird part is I have the same code later on and it works just fine. Any help would be appreciated. Thanks!

[Read this](https://stackoverflow.com/q/10714251/1188513), rewrite your code. — Mathieu Guindon, Sep 01 '17 at 21:12

score 0 · Answer 1 · answered Sep 01 '17 at 21:11

0

Easy fix. I have to select the sheet before trying to select the Range is all. Nevermind!

answered Sep 01 '17 at 21:11

bjefko

45
4

2

`Select` and `Activate` are poison. Don't work with the `Selection`, work with the object model. See the link in my comment under your question. Also unqualified `Range` calls are going to bite you in the rear end, one day or another. – Mathieu Guindon Sep 01 '17 at 21:12

score 0 · Answer 2 · answered Sep 01 '17 at 21:14

0

the command is a.select ... a.entirerow ... .... a is a range ... do not use a.select though .... use a.find ....

answered Sep 01 '17 at 21:14

jsotola

2,238
1
10
22

For each with named Ranges

2 Answers2