Syntax for Selecting Columns in VBA

[HedgePig]

Hello

Is there a way to select columns (more than 1!) in VBA by specifying numbers rather than letters?

Instead of
Columns("B:D").Select
I would like something along the lines of
Columns(2,4).Select 'Select columns 2 through 4

I want to do it this way because I want to select columns by using a variable start position and a variable end position - and don't want to end up converting those numbers to letters in a text string.

Thank you
HedgePig

 

[HedgePig]

And here, Mr Slowcoach HedgePig is the answer

Range(Columns(2), Columns(4)).Select

Why didn't you think of that BEFORE you posted? Ah well, at least now you know.

Regards
HedgePig
This message was edited by HedgePig on 2003-01-24 17:36

 

[GBrett]

This is a supplementary question to an old post, so ... anyway, please respond if you can help.

I have a similar need:

I have 4 column numbers (17, 23, 77, 127) which I have assigned to variables col1, col2 col3 and col4
I would like to select them and keep selected (like pressing Cntrl-click in windows) so I can add a chart using the data within them.

I guess I need to use a range, probably something like:

Range (col1:col1,col2:col2,col3:col3,col4:col4).select

but what is the actually syntax I need to be using?



Code snipet I am using to find a column (there are 4 in all... looking for different colum headers):

Cells.Find(What:="total)\% Free Space", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
col1 = ActiveCell.Column

 

[snowblizz]

I think the union function is what you need.
from the Excel help:

Set bigRange = Application.Union(Range("Range1"), Range("Range2"))

Waht you want is something like this:

Set bigRange = Application.Union(Range(Columns(17)), Range(Columns(23),Range(Columns(77),Range(Columns(127))

And then call on "bigRange". Not tried it myslef though.
However if you are adding ranges to a chart with VBA you can probably do that directly and not via .Selection

Replacing the numbers with col1 col2 and so on...

 

[GBrett]

Thanks .. that certainly looks promising. I'll give it a try.

A couple of question occurred to me though, whilst I was reading your reply.

a) Why does the syntax use SET? Would it not achieve the same without it?
b) Does the syntax "as is" highlight the columns (so I can go straight into the Charts.Add), or do I need to apply the variable BigRange to some other code line

BigRange.select

As you may have guessed ... this is my very first attempt at vba!
I applogise for the "noddyness" of these questions.

 

[GBrett]

SET seems to assign a variable to a (perhaps) more complication OBJECT statement.

So, I guess I could hang properties off the variable BigRange.

I see the relevance of SET now.

 

[aviadht]

I want to select columns with parameters

parameter 1: col1
parameter 2:col2

and the next one not working: columns(col1,col2).select

why ?

Aviad

 

[snowblizz]

Because the syntax used is not correct. Columns() only accepts one value, an integer of the column number. AFAIK

Please see my reply above for an example that seems to work.

 

[wadings]

Hi guys,

Who can help?

Which VBA code can i use to select discrete columns

e.g. I want to select columns A and J only. But I want to select them using integers so that i can apply a loop on them.

 

[VoG]

Try

Union(Columns(1), Columns(10)).Select