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

VBA - Select Multiple Columns using Column Number

This is a discussion on VBA - Select Multiple Columns using Column Number within the Excel Questions forums, part of the Question Forums category; Is there a way to select multiple columns in VBA using the column number? I have this Code: Range("D:E,G:H,J:K,M:N").Select Selection.Delete ...

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357

    Default VBA - Select Multiple Columns using Column Number

    Is there a way to select multiple columns in VBA using the column number?

    I have this
    Code:
    Range("D:E,G:H,J:K,M:N").Select
    Selection.Delete Shift:=xlToLeft
    But I would prefer to be able to select those columns using column numbers. It will not always be the same columns so using column numbers would be easier for me to code.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,212

    Default Re: VBA - Select Multiple Columns using Column Number

    Hi Cosmos75,

    It is unfortunately a bit verbose, but I believe still the simplest way:

    Union(Columns(14), Columns(13), Columns(11), _
    Columns(10), Columns(8), Columns(7), _
    Columns(5), Columns(4)).Delete Shift:=xlToLeft

    Damon

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357

    Default Re: VBA - Select Multiple Columns using Column Number

    Damon,

    Thank you for your reply!

    That example works great if I know in advance the number of columns I wanted to delete. Theoretically, I would always delete the same number of columns but I just want to cover the oddd chance that I may have to delete more (or less) than that particular number of columns.

    I wonder if I can add to Union() via a loop of some sort?

    That at least gives me a starting point.
    THANK YOU!

  4. #4
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    17,003

    Default Re: VBA - Select Multiple Columns using Column Number

    Perhaps something like this:

    Code:
    Sub delcols()
    Dim x, y As Range, z As Integer
    x = Array(14, 13, 11, 10, 8, 7, 5, 4)
    Set y = Columns(x(0))
    For z = 1 To UBound(x)
    Set y = Union(y, Columns(x(z)))
    Next z
    y.Delete Shift:=xlToLeft
    End Sub

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357

    Default Re: VBA - Select Multiple Columns using Column Number

    HOTPEPPER,

    Wonderful!

    I was trying to use and array and add it back into the Union but had no idea what the syntax should be.
    Code:
    Set y = Union(y, Columns(x(z)))
    That's what I was missing! Not that I'd ever figure that out on my own!

    THANK YOU!

  6. #6
    Board Regular
    Join Date
    Jan 2009
    Location
    India
    Posts
    284

    Default Re: VBA - Select Multiple Columns using Column Number

    Hi all,

    I just want to understand how many columns can I input in the Array.
    x = Array(14, 13, 11, 10, 8, 7, 5, 4)


    I was using the following and its not working:

    x = Array(2,3,9,10,12,13,16,17,18,20,21,22,23,24,25,26,27,3,29,30,31,30,33,34,35,37,49,50,51,52,53,54,55,5,57,58,59,60,61,82 ,83,84,97,98,99,100,101,102,103,104,105,106)

    Actually, I was using a variable to pick the required column number and passing it in the above argument..something as below

    X = Array(My_Array) where My_Array = 2,3,9,10,12,13,16,17,18,20,21,22,23,24,25,26,27,3,29,30,31,30,33,34,35,37,49,50,51,52,53,54,55,5,57,58,59,60,61,82,83,84 ,97,98,99,100,101,102,103,104,105,106


    Kindly suggest.
    Last edited by pavin; Oct 12th, 2011 at 05:05 AM.
    Regards,
    Praveen.
    _____________________________________________

    Our friends should be companions who inspire us, who help us rise to our best....Joseph B. Wirthlin

  7. #7
    New Member
    Join Date
    Nov 2011
    Posts
    1

    Question Re: VBA - Select Multiple Columns using Column Number

    Pravin,
    Excel doesn't like when the column reference number is greater than 26 (columns AA, AB etc).

    So, if you are deleting columns past column Z, you will need to delete them 26 at a time.
    For example: if you want to delete columns 24 to 29, you will write

    Union(Columns(24), Columns(25), Columns(26)).Delete Shift:=xlToLeft
    Union(Columns(24), Columns(25), Columns(26)).Delete Shift:=xlToLeft

    Yes, it is the exact same line written twice. Note, Column 24 in line 2 is actually column 27. column 27 moved to column 24 when you deleted 24, 25 and 26 in the first line.

    Good luck!

  8. #8
    New Member
    Join Date
    Nov 2013
    Posts
    1

    Default Re: VBA - Select Multiple Columns using Column Number

    I know this is old but for anyone else who may come across this thread, a much easier way to do this would be to do the following:

    Dim x as Integer
    Dim y as Integer

    x = 1 ' For A
    y = 4 ' For D

    Columns(Chr(64 + x) & ":" & Chr(64 + y)).Select

    This would select columns A to D.
    using this format will allow you to easily select any range of columns from A to Z using completely variable values. I am sure with a bit of creativity you could also write a function that would enable selections of any range of columns past Z. (ie IF x > 26 then ...)

  9. #9
    New Member
    Join Date
    Jan 2014
    Posts
    1

    Default Re: VBA - Select Multiple Columns using Column Number

    Adding this as it was the first result of my google search, despite the age:

    The following function will convert a column number into its corresponding column string.
    Code:
    Function CNTS(intCol As Integer) As String    
        CNTS = Cells(1, intCol).Address(1, 0)
        CNTS = Left(CNTS, InStr(CNTS, "$") - 1)
    End Function
    No error checking, but it will work for any valid column number. You can use something similar to convert an entire array into a valid address with a little work. You could even have it read the array, re-order the elements ascending and group sequential numbers into a single reference (IE: Passing 1, 3, 5, 2, 12 to return A:C,E,L)

    A little more detailed than I have time to go into now, but it's possible.

  10. #10
    New Member
    Join Date
    Jan 2014
    Posts
    1

    Default Re: VBA - Select Multiple Columns using Column Number

    Is there a way to select multiple columns in VBA using the column number?
    Yes, there is...and from this thread there are multiple ways. Here is what I use for a contiguous set of Columns, from the first one to delete to the last.

    ws.Range(Cells(1, 2), Cells(ws.Rows.count, ws.UsedRange.Columns.count)).Delete

    If you want to use and array to do the deletions I would suggest something like

    Type ColumnRange
    LeftColNum as Integer
    RightColNum as Integer
    Type End

    Dim ColsToDelete() as ColumnRange

    Redim ColsToDelete()



    For i = Lbound(ColsToDelete) to Ubound(ColsToDelete)
    ws.Range(Cells(1, ColsToDelete(i).LeftColNum ), Cells(ws.Rows.count, ColsToDelete(i).RightColNum).Delete
    Next i

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