Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i select a column when using "exact" formula? | Excel Discussion (Misc queries) | |||
"Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
Questionnaire sheet: Select "yes" or "no," and only one can be selected | Excel Worksheet Functions | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming | |||
"Select Column method or property not available because some/all of object doesn't refer to table" | Excel Programming |