Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Syntax for Selecting Columns in VBA

This is a discussion on Syntax for Selecting Columns in VBA within the Excel Questions forums, part of the Question Forums category; Hello Is there a way to select columns (more than 1!) in VBA by specifying numbers rather than letters? Instead ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default

    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

  2. #2
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Jun 2009
    Posts
    7

    Default Re: Syntax for Selecting Columns in VBA

    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
    Last edited by GBrett; Jun 10th, 2009 at 04:42 AM.

  4. #4
    Board Regular
    Join Date
    Mar 2009
    Location
    Turku, Finland
    Posts
    1,123

    Default Re: Syntax for Selecting Columns in VBA

    I think the union function is what you need.
    from the Excel help:
    Code:
    Set bigRange = Application.Union(Range("Range1"), Range("Range2"))
    Waht you want is something like this:
    Code:
    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...
    Last edited by snowblizz; Jun 10th, 2009 at 04:44 AM. Reason: edit after seeing edit!

  5. #5
    New Member
    Join Date
    Jun 2009
    Posts
    7

    Default Re: Syntax for Selecting Columns in VBA

    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.

  6. #6
    New Member
    Join Date
    Jun 2009
    Posts
    7

    Default Re: Syntax for Selecting Columns in VBA

    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.

  7. #7
    Board Regular
    Join Date
    Aug 2008
    Posts
    91

    Default Re: Syntax for Selecting Columns in VBA

    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

  8. #8
    Board Regular
    Join Date
    Mar 2009
    Location
    Turku, Finland
    Posts
    1,123

    Default Re: Syntax for Selecting Columns in VBA

    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.

  9. #9
    New Member
    Join Date
    Sep 2011
    Posts
    9

    Post Re: Syntax for Selecting Columns in VBA

    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.

  10. #10
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,131

    Default Re: Syntax for Selecting Columns in VBA

    Try

    Code:
    Union(Columns(1), Columns(10)).Select
    HTH, Peter
    Please test any code on a copy of your workbook.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com