Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop over columns | Excel Discussion (Misc queries) | |||
need some help with selecting a sheet in a loop again | Excel Programming | |||
How to select columns in loop | Excel Programming | |||
loop through columns | Excel Programming | |||
Loop 20 columns Help! | Excel Programming |