norsemanGrey
norsemanGrey

Reputation: 355

How to Reference a Named Column in a Range of Rows in Excel VBA

I want to loop through all the cells in in a named column of a set of rows selected by the user. However, I am not sure how to accomplish this.

Named Column in Selection of Rows

I have used the blow code to get the user selected range / rows, but I do not know how to go from there to get hold of the cells in the named column within this range.

Set rng = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)

Upvotes: 2

Views: 333

Answers (2)

JvdV
JvdV

Reputation: 75840

You can use Intersect:

Sub Test()

Dim rng1 As Range, rng2 As Range, cl As Range
With ThisWorkbook.Sheets("Sheet1") 'Whichever sheet your working on
    Set rng1 = Application.InputBox("Select a range", "Obtain Range Object", Type:=8)
    Set rng2 = Intersect(rng1, .Range("COLE")) 'Whichever name your named range is.
    For Each cl In rng2
        'Do something
    Next cl
End With    

End Sub

Note that upon canceling the inputbox, this will throw an error which you can solve with some error handling.

Upvotes: 1

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

Here are two ways to loop through every cell in the range rng and prove we visited each of them.

Dim r as Range
For Each r in rng
    r = "visited" 
Next r

And

Dim i As Long
For i = 1 To rng.Cells.Count
    rng.Cells(i) = "visited"
Next i

Upvotes: 0

Related Questions