CODEWITHSUNDEEP
excelvba
lolikianoaholting
lolikianoaholting

Reputation: 31

Combining Multiple Separated Columns Selection

I'm creating an application for user where the user can select certain cells(non-overlapping) and I need to combine them into one or more multiple ranges that can be iterated by row, hopping the cells that are not selected. For example, if the users selects A5:A15, C5:C15, E5:F15 in one selection, the iteration will be: A5, C5, E5, F5, A6, C6, and so on. When I'm using Selection, it iterates every row from column A instead, then after processing row 15, the cells in Column C start to be processed, and the last are E and F combined together. Is there a way to combine them so I can iterate row by row instead of by "cluster" of selection?

Upvotes: 0

Views: 51

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Please, test the next code. It will iterate in the way you need. But, it will skip the cells E1 to E4...

Sub IterationOrderDiscontinuousRange()
   Dim rng As Range, itRng As Range, Cel As Range, i As Long
   Set rng = Range("A5:A15,C5:C15,E1:F15")

   For i = 1 To rng.rows.Count
        Set itRng = Intersect(rng, ActiveSheet.rows(i + rng.row - 1))
        For Each Cel In itRng.cells
            Debug.Print Cel.Address: Stop 'press F5 to continue
        Next
   Next i
End Sub

If you want iteration by (all) rows, meaning to start with E1 to E4, then what the above code does, I can adapt it. But, I am thinking that it is maybe matter of a mistake in setting the last area range...

It is good to know that the range row is the row of its first cell.

If neither the above option is not good enough, you should better state what you really want...

Upvotes: 1

Related Questions