Selecting columns through a loop
 

Hi,

I would like to select columns through loop so that all selected columns
would be highlighted (like CRTL+select operation) for operations later on.

Through recording Excel gave me this codes for the first three alternating
columns:
Sub Macro1( )
Range("B:B,D:D,F:F").Select
Range("F1").Activate
End Sub

I tried to write a code, but it would not hold the columns highlighted
(selected)! I was wondering what I am doing wrong. Thanks.

For i = 1 To 3
n = 2 * i
ActiveSheet.Columns(n).Select
Selection.Activate
Next
End Sub

Selecting columns through a loop
 

Hi,

Try something like:

Sub Test( )
Dim n as Long, i as Long
Dim rg as range

For i = 1 To 3
n = 2 * i
if rg is Nothing then 'case 'first time
set rg = ActiveSheet.Columns(n)
else 'case general
set rg=application.union(rg,ActiveSheet.Columns(n))
end if
Next
rg.Select

End Sub


--
Regards,
Sébastien


"GreenInIowa" wrote:

Hi,

I would like to select columns through loop so that all selected columns
would be highlighted (like CRTL+select operation) for operations later on.

Through recording Excel gave me this codes for the first three alternating
columns:
Sub Macro1( )
Range("B:B,D:D,F:F").Select
Range("F1").Activate
End Sub

I tried to write a code, but it would not hold the columns highlighted
(selected)! I was wondering what I am doing wrong. Thanks.

For i = 1 To 3
n = 2 * i
ActiveSheet.Columns(n).Select
Selection.Activate
Next
End Sub

Selecting columns through a loop
 

One way:

Dim rSelect As Range
Dim i As Long
With ActiveSheet
Set rSelect = .Columns(2)
For i = 2 To 3
Set rSelect = Union(rSelect, .Columns(i * 2))
Next i
End With
rSelect.Select


In article ,
"GreenInIowa" wrote:

Hi,

I would like to select columns through loop so that all selected columns
would be highlighted (like CRTL+select operation) for operations later on.

Through recording Excel gave me this codes for the first three alternating
columns:
Sub Macro1( )
Range("B:B,D:D,F:F").Select
Range("F1").Activate
End Sub

I tried to write a code, but it would not hold the columns highlighted
(selected)! I was wondering what I am doing wrong. Thanks.

For i = 1 To 3
n = 2 * i
ActiveSheet.Columns(n).Select
Selection.Activate
Next
End Sub

Selecting columns through a loop
 

Thanks, sebastienm!



"sebastienm" wrote:

Hi,

Try something like:

Sub Test( )
Dim n as Long, i as Long
Dim rg as range

For i = 1 To 3
n = 2 * i
if rg is Nothing then 'case 'first time
set rg = ActiveSheet.Columns(n)
else 'case general
set rg=application.union(rg,ActiveSheet.Columns(n))
end if
Next
rg.Select

End Sub


--
Regards,
Sébastien


"GreenInIowa" wrote:

Hi,

I would like to select columns through loop so that all selected columns
would be highlighted (like CRTL+select operation) for operations later on.

Through recording Excel gave me this codes for the first three alternating
columns:
Sub Macro1( )
Range("B:B,D:D,F:F").Select
Range("F1").Activate
End Sub

I tried to write a code, but it would not hold the columns highlighted
(selected)! I was wondering what I am doing wrong. Thanks.

For i = 1 To 3
n = 2 * i
ActiveSheet.Columns(n).Select
Selection.Activate
Next
End Sub

Selecting columns through a loop
 

Thanks, McGimpsey!



"JE McGimpsey" wrote:

One way:

Dim rSelect As Range
Dim i As Long
With ActiveSheet
Set rSelect = .Columns(2)
For i = 2 To 3
Set rSelect = Union(rSelect, .Columns(i * 2))
Next i
End With
rSelect.Select


In article ,
"GreenInIowa" wrote:

Hi,

I would like to select columns through loop so that all selected columns
would be highlighted (like CRTL+select operation) for operations later on.

Through recording Excel gave me this codes for the first three alternating
columns:
Sub Macro1( )
Range("B:B,D:D,F:F").Select
Range("F1").Activate
End Sub

I tried to write a code, but it would not hold the columns highlighted
(selected)! I was wondering what I am doing wrong. Thanks.

For i = 1 To 3
n = 2 * i
ActiveSheet.Columns(n).Select
Selection.Activate
Next
End Sub

Selecting columns through a loop
 

Just one other tip for you around the For/Next structure. Rather than using
n=2*i, you could use For i = 2 To 6 Step 2, which would result in i=2, 4,
and 6. Gives you one less variable to deal with :-)

"sebastienm" wrote in message
...
Hi,

Try something like:

Sub Test( )
Dim n as Long, i as Long
Dim rg as range

For i = 1 To 3
n = 2 * i
if rg is Nothing then 'case 'first time
set rg = ActiveSheet.Columns(n)
else 'case general
set rg=application.union(rg,ActiveSheet.Columns(n))
end if
Next
rg.Select

End Sub


--
Regards,
Sébastien


"GreenInIowa" wrote:

Hi,

I would like to select columns through loop so that all selected columns
would be highlighted (like CRTL+select operation) for operations later
on.

Through recording Excel gave me this codes for the first three
alternating
columns:
Sub Macro1( )
Range("B:B,D:D,F:F").Select
Range("F1").Activate
End Sub

I tried to write a code, but it would not hold the columns highlighted
(selected)! I was wondering what I am doing wrong. Thanks.

For i = 1 To 3
n = 2 * i
ActiveSheet.Columns(n).Select
Selection.Activate
Next
End Sub