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.

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.

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.

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.

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

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.

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

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

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.