Excel VBA Code to Reference Column Numbers Instead of Letters

Often times we want to reference a column with numbers instead of letters in VBA.  The following macro contains various examples of how to reference one or more columns by the column number in Excel.

This is great if you are basing the column reference on a formula result, variable, or cell's value.

Sub Column_Number_References()
'This macro contains VBA examples of how to
'reference or select a column by numbers instead of letters
    
Dim lCol As Long
    
    'The standard way to reference columns by letters
    Range("A:B").Select
    Range("A:A").Select
    Columns("A").Select
    Columns("A:F").Select
     
    'Select a single column with Columns property
    'The following line selects column B (column 2)
    Columns(2).Select
     
    'Select columns based on a variable
    lCol = 2
    Columns(lCol).Select
     
    'Select columns based on variable of a cell's value
    'Cell A1 must contain a valid column number.
    lCol = Range("A1").Value
    Columns(lCol).Select
     
    'Select multiple columns with the Range and Columns properties
    'The following line selects columns B:E
    Range(Columns(2), Columns(5)).Select
     
    'Select multiple columns with the Columns and Resize properties
    'The following line selects columns B:D. 
    'It starts a column 2 and resizes the range by 3 columns.
    'The row parameter of the resize property is left blank.  
    'This is an optional parameter (argument) and is
    'not required for the column resize.
    Columns(2).Resize(, 3).Select

End Sub

Please leave a comment below with any questions. Thanks!

  • HELLO
    VERY VERY USEFUL WAS FOR ME THIS ARTICLES
    I LIKED THIS AND WAS VERY HELPFUL FOR ME
    THANKS A LOT OF

  • Thank you for the comments on range cells.
    I’m trying to delete multiple columns at once time but it comes with error : compile error (sub and function not defined)

    n = “5_8_2019_AUS”
    With Worksheet(n)
    .Range(.Columns(4), .Columns(6)).Delete
    End With

  • Many thanks for this. It is indeed very helpful. I was wondering if there is a similar code to select non-continuous columns .. for example how will one select column 1, 3 and 5 at the same time using a vba code.

  • To add to my comment, I found a solution. Here it is for your review
    The question was : How to select multiple non-continuous columns.
    The answer is :

    Union(Range(Columns(1), Columns(1)), Range(Columns(3), Columns(3)), Range(Columns(pucFirstColumnQtr – 1), Columns(pucFirstColumnQtr – 1)), Range(Columns(pucLastColumnQtr + 1), Columns(pucLastColumnQtr + 1))).Select

  • Range(Columns(2), Columns(3)).Select – this doesn’t select strictly columns B:C if you have a merged cell between B:D somewhere .

  • Search
    Generic filters
    Exact matches only

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    Free Excel Training Webinar Modern Power Tools

    >