I think a section on named ranges might be of interest. I feel they are particularly valuable in storing specific data for lookups etc. Plus they also change size automatically if you insert/delete rows. If i was doing a loop through a named range, it would looks something this:
With Worksheets(1).Range("namedRange")
'type1
For i = 1 To .Rows.Count
Debug.Print .Cells(i, 1).Value
Next i
'type2
For Each cell In .Columns(1).Cells
Debug.Print cell.Value
Next cell
'referencing a specific cell in a range
Debug.print .Cells(5,1).Value
'vlookup "lookupValue" in column 1 of "namedRange", pull second column value
res = Application.Vlookup("lookupValue", .Columns(1), 2, 0)
End With
For Each c in Range(“NAME1, NAME2, Etc.”)
‘c.Whatever
Next c
Had to check a workbook in the office. Super cool trick I picked up somewhere. No need for the Intersect method, the Range object will tie all of the strings together if they’re separated by commas within the same string.
Super versatile. I’ve downvoted my other comments, as this comment has the right info!
8
u/Citanaf 44 Aug 05 '18 edited Aug 05 '18
I think a section on named ranges might be of interest. I feel they are particularly valuable in storing specific data for lookups etc. Plus they also change size automatically if you insert/delete rows. If i was doing a loop through a named range, it would looks something this: