Reputation: 355
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
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
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