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!

  • 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

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

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

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >