Stepping Through a Non-Contiguous Range of Cells

Alan

Thanks for the feedback, I was definitely on the wrong track. It was good to see a post by Gary's Student again.

Hi Peter, I found an answer on Stack Overflow, to loop through my non-contiguous selection by columns, see:

https://stackoverflow.com/questions/54219683/non-contiguous-for-each-loop-per-row-instead-of-column

It also has an image of my selection (example).

Thanks for your help.
Alan

Alan

I remembered the shorter way before going to bed last night.

Sub ER()
Dim r1 As Range, r2 As Range, r3 As Range, rng As Range
Dim c As Range

Set c = ActiveCell
c.Resize(1 + 2, 1).Select
With Selection
Set r1 = .Offset(0, 0)
Set r2 = .Offset(0, 2)
Set r3 = .Offset(0, 4)
End With
Set rng = Application.Union(r1, r2, r3)
rng.Select
End Sub

Alan

I'm not happy with this but it works. If the Offsets are wrong change them for address1 and addr2

Sub ExtendRange()
'suggested shortcut key Ctrl + Shift + R

Dim Col As Integer, l As Long
Dim rng As Range, j As Integer
Dim addr0 As String, addr1 As String, addr2 As String

With ActiveCell
l = .Row
Col = .Column
addr0 = Range(Cells(l, Col), Cells(l + 2, Col)).Address
End With
addr1 = Range(Cells(l, Col + 2), Cells(l + 2, Col + 2)).Address
addr2 = Range(Cells(l, Col + 4), Cells(l + 2, Col + 4)).Address
Set rng = Application.Union(Range(addr0), Range(addr1), Range(addr2))

rng.Select

End Sub

Hi Peter, I was looking for a way to do it in VBA?

Note I also need to determine the address or at least sheet row so I can then refer to other (non-selected) cells on the same row(s).

Alan

Select the non-contiguous range before entry, then tab or press Enter and the you move ithrough each column before going to the next.

A For Each steps through the non-contiguous selection per row (e.g. row 1 cell (column) 1,2,3; row 2 cell 1,2,3). I'd like to cycle through the selection by columns (e.g. column 1 cell (row) 1,2,3; column 2 row 1,2,3 etc.).

What is the easiest way to do this?

I don't necessarily need code, just an explanation and I'll be able to code it myself...

(I also need to access the address (in part) of each in order to refer to other cells on the same row).

Regards
Alan