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

Question

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)

Answer 1

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.