Select every "odd" column
 

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

Select every "odd" column
 

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.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Jason Morin" wrote in message
...
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

Select every "odd" column
 

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.

Select every "odd" column
 

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


"Jason Morin" wrote in message
...
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