Select every "odd" column

[Jason Morin]

Hello and TIA for your help. I'm looking for the quickest
way to select every odd column of a worksheet in VBA.
Here are 2 methods I have, although the 1st one doesn't
select column IU.

1) My solution (not selecting col. IU - why?):

Sub test()
Dim i As Integer
Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim col As Range
Set ws = ActiveWorkbook.ActiveSheet
Set col = ws.Range("A:A")
For Each col In ws.Columns
If col.Column Mod 2 = 1 Then
If rng2 Is Nothing Then
Set rng2 = rng1
Else
Set rng2 = Union(rng2, rng1)
End If
Set rng1 = col
End If
Next
rng2.Select
End Sub

2) Macro Recorder:

Range("A:A,C:C,E:E,G:G,I:I,etc.,etc.").Select

 

[Rob van Gelder]

Sub test()
Dim i As Long, rng As Range

For i = 1 To Columns.Count Step 2
If rng Is Nothing Then
Set rng = Columns(i)
Else
Set rng = Union(rng, Columns(i))
End If
Next
rng.Select
End Sub

You wouldn't construct a string and go Range(str).Select because str would
have to be less than 256 characters.
Some people think that Union is slow - though I've never tested. You could
try a hybrid solution ie Union at every 256 characters.
Probably more of an issue with Rows than Columns to be honest.

 

[Chip]

On Solution 1, add this on the line after the Next Statement:

Set rng2 = Union(rng2, rng1)
Need to do it one more time the way your loop ends.

 

[Trevor Shuttleworth]

Jason

one way:

Sub test()
Dim rng As Range
Dim col As Range
For Each col In Columns
If col.Column Mod 2 = 1 Then
If rng Is Nothing Then
Set rng = col
Else
Set rng = Union(rng, col)
End If
End If
Next
rng.Select
End Sub

Regards

Trevor