Referencing Cells within Named Range in VBA

4
votes

I'd like to know if its possible to use Cells(r, c) within a named range in VBA. Ex.: I have named range "Test" to be A1:B5 and I am working on a Macro that loops through this range, however I'd like to avoid explicit declarations of ranges as much as possible, so sheet manipulation can be as easier as possible.

In case what I said wasnt possible, I basically need to be able to loop/write through cells in the named ranges, if there is another approach for this I'd be more than glad to get a suggestion.

Thanks!

2
vbaexcel

2 Answers

6
votes

Sure, you can simply use e. g.

Worksheets("name").Range("Test").Cells(r, c)

and 

Dim c As Range
For Each c In Worksheets("Name").Range("Test").Cells
    Debug.Print c.Address
Next
1
votes

I figured it out with a bit more of research In case anyoone drops by wondering this:

Sub test()
    dim r1 as Range
    set r1 = Range("NamedRange")

    f = r1.row
    l = Range("NamedRange").Row + Range("NamedRange").Rows.Count - 1

    Do while (Cells(f,1) <> "" and f <= l
        'Run code
    Loop
end sub