Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
Excel 2003
What is the syntax for selecting multiple noncontiguous columns using column numbers instead of letters? Example, I only know the column numbers I want selected are 3, 5, 8-10, and 12. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
Kludgy, but works
Union(Columns(3), Columns(5), Columns(8), Columns(9), Columns(10), Columns(12)).Select -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "c mateland" wrote in message oups.com... Excel 2003 What is the syntax for selecting multiple noncontiguous columns using column numbers instead of letters? Example, I only know the column numbers I want selected are 3, 5, 8-10, and 12. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
It's a bit long winded but you could use
Sub SelectNonContigCols() Range(Chr(64 + 3) & ":" & Chr(64 + 3) & "," & Chr(64 + 5) & ":" & Chr(64 + 5) & "," _ & Chr(64 + 8) & ":" & Chr(64 + 10) & "," & Chr(64 + 12) & ":" & Chr(64 + 12)).Select End Sub Alan c mateland wrote: Excel 2003 What is the syntax for selecting multiple noncontiguous columns using column numbers instead of letters? Example, I only know the column numbers I want selected are 3, 5, 8-10, and 12. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
"c mateland" skrev i en meddelelse
oups.com... Excel 2003 What is the syntax for selecting multiple noncontiguous columns using column numbers instead of letters? Example, I only know the column numbers I want selected are 3, 5, 8-10, and 12. Thanks. One way: Sub SelectColumns() 'Leo Heuser, 20 Oct. 2006 Dim ColArray As Variant Dim Counter As Long Dim SelectColumns As Range ColArray = Array(3, 5, 8, 9, 10, 12) With Sheets("Sheet1") Set SelectColumns = .Columns(ColArray(LBound(ColArray))) For Counter = LBound(ColArray) + 1 To UBound(ColArray) Set SelectColumns = Union(SelectColumns, ..Columns(ColArray(Counter))) Next Counter End With SelectColumns.Select End Sub -- Best regards Leo Heuser Followup to newsgroup only please. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
And on a readable form :-)
Sub SelectColumns() 'Leo Heuser, 20 Oct. 2006 Dim ColArray As Variant Dim Counter As Long Dim SelectColumns As Range ColArray = Array(3, 5, 8, 9, 10, 12) With Sheets("Sheet1") Set SelectColumns = .Columns(ColArray(LBound(ColArray))) For Counter = LBound(ColArray) + 1 To UBound(ColArray) Set SelectColumns = _ Union(SelectColumns, .Columns(ColArray(Counter))) Next Counter End With SelectColumns.Select End Sub Leo heuser |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
Bob, thank you for the solution. I never thought about using Union that
way. -Chuck Bob Phillips wrote: Kludgy, but works Union(Columns(3), Columns(5), Columns(8), Columns(9), Columns(10), Columns(12)).Select -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "c mateland" wrote in message oups.com... Excel 2003 What is the syntax for selecting multiple noncontiguous columns using column numbers instead of letters? Example, I only know the column numbers I want selected are 3, 5, 8-10, and 12. Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
Thanks, Leo. I'm surprised to find out how difficult it is to select
multiple noncontiguous columns by number. But I'm glad to get some answers. -Chuck Leo Heuser wrote: And on a readable form :-) Sub SelectColumns() 'Leo Heuser, 20 Oct. 2006 Dim ColArray As Variant Dim Counter As Long Dim SelectColumns As Range ColArray = Array(3, 5, 8, 9, 10, 12) With Sheets("Sheet1") Set SelectColumns = .Columns(ColArray(LBound(ColArray))) For Counter = LBound(ColArray) + 1 To UBound(ColArray) Set SelectColumns = _ Union(SelectColumns, .Columns(ColArray(Counter))) Next Counter End With SelectColumns.Select End Sub Leo heuser |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
"c mateland" skrev i en meddelelse
ups.com... Thanks, Leo. I'm surprised to find out how difficult it is to select multiple noncontiguous columns by number. But I'm glad to get some answers. -Chuck You're welcome Chuck. Thanks for your feedback. Leo Heuser |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select noncontiguous multiple columns by column number?
Another way just for curiosity, but I'd go with Leo's
Sub MultiCol() Dim bAdj As Boolean Dim i As Long Dim sAddr As String Dim rng As Range Dim varr varr = Array(1, 5, 8, 9, 10, 12) ' OP's 'varr = Array(2, 10, 27, 53, 105, 256) 'another test sAddr = "C[" For i = LBound(varr) To UBound(varr) - 1 'ought union later if any adjacent columns If varr(i + 1) = varr(i) + 1 Then bAdj = True 'offset from Col-1 so subtract 1 sAddr = sAddr & varr(i) - 1 & "],C[" Next sAddr = sAddr & CStr((varr(UBound(varr)) - 1)) & "]" sAddr = Application.ConvertFormula(sAddr, xlR1C1, xlA1, , Range("a1")) If bAdj = True Then Set rng = Union(Range(sAddr), Range(sAddr)) Else Set rng = Range(sAddr) End If rng.Select Debug.Print rng.Address(0, 0) End Sub This would fail with over 36 3-digit column numbers due to address length limit. Regards, Peter T "c mateland" wrote in message oups.com... Excel 2003 What is the syntax for selecting multiple noncontiguous columns using column numbers instead of letters? Example, I only know the column numbers I want selected are 3, 5, 8-10, and 12. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average numbers in noncontiguous columns ignoring 0's. | Excel Worksheet Functions | |||
Select Number of Columns and Print Macro | Excel Discussion (Misc queries) | |||
Using Multiple, Noncontiguous Ranges in COUNTIF? | Excel Discussion (Misc queries) | |||
Print titles: can noncontiguous columns at left be printed & how? | Excel Worksheet Functions | |||
VBA to select multiple columns | Excel Programming |