Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10,593
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 138
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 266
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 266
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 52
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 266
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average numbers in noncontiguous columns ignoring 0's. bean counter Excel Worksheet Functions 2 April 16th 10 01:23 PM
Select Number of Columns and Print Macro Don Excel Discussion (Misc queries) 0 November 10th 06 10:26 PM
Using Multiple, Noncontiguous Ranges in COUNTIF? PBJ Excel Discussion (Misc queries) 0 October 11th 06 05:45 PM
Print titles: can noncontiguous columns at left be printed & how? HatMar Excel Worksheet Functions 2 November 21st 05 09:47 PM
VBA to select multiple columns Marvin Excel Programming 11 October 1st 04 02:35 PM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"