Looping over non-contiguous column selection

[Guest]

I'd like to loop over all columns (or rows) in a non-contiguous selection in
one of my macros.

I can already do it rather easily for a contiguous column selection by
referencing the Selection.Column property and the Selection.Columns.Count
property as in the following ...

'get DataColumn information
FirstDataColumn = Selection.Column
LastDataColumn = FirstDataColumn + Selection.Columns.Count - 1
For k = FirstDataColumn To LastDataColumn
....
next k

I'd like to do the same thing for a non-contiguous column/row selection.

Any help would be greatly appreciated.

Thanks,

Jim

 

[Peter T]

Hi Jim,

I don't follow quite what you want to do but maybe you can adapt something
from the following to your needs,

Sub test()
Dim i As Long
Dim rng As Range
Dim rAr As Range
Dim rCol As Range

Set rng = Range("A1:C4,D5:F8,G9:I12")

For Each rAr In rng.Areas
For Each rCol In rAr.Columns
i = i + 1
rCol.Value = i
Next
Next

End Sub

Regards,
Peter T

 

[Guest]

Thanks Peter,

Based on your input we wound up doing something like this ...

Sub Test2()
Const REDINDEX = 3
Dim k As Integer
Dim FirstColumn As Integer
Dim LastColumn As Integer
Dim RangeArea As Range

For Each RangeArea In Selection.Areas
FirstColumn = RangeArea.Column
LastColumn = FirstColumn + RangeArea.Columns.Count - 1
For k = FirstColumn To LastColumn
Columns(k).Interior.ColorIndex = REDINDEX
Next k
Next
End Sub

In this example, we wanted to select .. an arbitrary number of
non-contiguous columns (say columns 1, 4, and 5 for example), and do some
data manipulation or formatting for only the data that exists in those
columns. In the code above, we simply changed the fill color of all cells in
the selected columns to red.

We wanted to be able to do this for any column(s) of our choosing, not just
columns that exist next to each other, nor did we want to "hard-code" the
column selection into the subroutine. Your example pointed us in the right
direction.

Thanks,

Jim