Iterate through non contiguous range of cells

[Trebormac]

Hi All,

I need to iterate through a bunch of cells that are contiguous, e.g., A1077, A1076, B1086, B1087, A1095 etc and take some action based on the cell vaalue. There are about a hundred or so cells that I need to examine.

What is the best way to handle this...should I put the cell addresses in an array?

Thanks for any suggestions!

Trebormac

 

[baitmaster]

If you are only ever using the same set of cells, you can create a named range that includes all of them. Select them all, and assign a name to the group, then use the defined name in your code

Alternatively, you can use UNION (range 1, range 2... range z) to link them into a single range programatically

 

[mikerickson]

You could use a range variable to loop through the range

Dim oneCell As Range

For Each oneCell In ThisWorkbook.Sheets("Sheet1").Range("A1077, A1076, B1086, B1087, A1095")
    MsgBox oneCell.Address
Next oneCell

 

[Trebormac]

Thanks these were both helpful!

Trebormac