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!