Excel VBA Columns Property
When we want to refer to the cell we use either the Range object or Cells property. Similarly how you do refers to columns in VBA? We can refer to columns by using “Columns” property in VBA. In this article, we will take you through how to use Columns property in Excel VBA coding.
Columns property is used to refer to columns in the worksheet. Using this property we can use any column in the specified worksheet and work with it. Look at the syntax of VBA COLUMNS property.
We need to mention the column number or column header alphabet to reference the column.
For example, if we want to refer the second column we can write the code in three ways.
Examples of Columns Property in VBA
If you want to select the second column in the worksheet then first we need to mention the column number we need to select.
Sub Columns_Example() Columns (2) End Sub
Now put a dot (.) to choose the “Select” method.
One of the problems with this property is we don’t get to see the IntelliSense list of VBA.
Sub Columns_Example() Columns(2).Select End Sub
So, the above VBA code will select the second column of the worksheet.
Instead of mentioning the column number we can also use column header alphabet “B” to select the second column.
Sub Columns_Example() Columns("B").Select Columns("B:B").Select End Sub
Both the above codes will select column B i.e. second column.
Example #1 – Select Column Based on Variable Value
We can also use the variable to select the column number. Look at the below code now.
Sub Columns_Example() Dim ColNum As Integer ColNum = 4 Columns(ColNum).Select End Sub
In the above, I have declared the variable as Integer and assigned the value of 4 to this variable.
For Columns property I have supplied this variable instead of column number. Since the variable holds the value of 4 it will select the 4th column.
Example #2 – Select Column Based on Cell Value
We have seen how to select the column based on variable value now we will see how we can select the column based on cell value number. In cell A1 I have entered the number 3.
Now below code will select the column based on the number in the cell A1.
Sub Columns_Example() Dim ColNum As Integer ColNum = Range("A1").Value Columns(ColNum).Select End Sub
The above code is the same as the previous one but the only thing I have changed here is instead of assigning the direct number to the variable I have given variable value as “whatever the number is there in the cell A1”.
Since we have a value of 3 in cell A1 it will select the third column.
Example #3 – Combination of Range & Column Property
We can also use VBA Columns property with Range object as well. Using Range object we can specify the specific range. For an example look at the below code.
Sub Columns_Example1() Range("C1:D5").Columns(2).Select End Sub
In the above example, I have specified the range of cells as C1 to D5, then using VBA Columns property I have specified the column number as 2 to select.
Now, in general, our second column is B and the code has to select the “B” column but see what happens when I run the code.
It has selected the cells from D1 to D5.
In our perception, it should have selected the second column i.e. column B. But now it has selected the cells from D1 to D5.
The reason why it has selected these cells because before using the VBA COLUMNS property I have specified the range by using the RANGE object as C1 to D5. Now column property thinks within this range as the columns and selects the second column in the range C1 to D5. D is the second column and specified cells are D1 to D5.
Example #4 – Select Multiple Columns with Range Object
Using the Range object and Columns property we can select multiple columns. Look at the below code.
Sub Columns_Example1() Range(Columns(2), Columns(5)).Select End Sub
The code will select the column from the second column to the fifth column i.e. from column B to E.
We can also write the code in this way as well.
Sub Columns_Example1() Range(Columns(B), Columns(E)).Select End Sub
The above is too exactly the same as the previous one and selects the columns from B to E.
Like this, we can use COLUMNS property in Excel VBA to work with columns in the worksheet.
This has been a guide to VBA Columns. Here we discuss examples of columns in excel VBA and select multiple columns with range object and downloadable excel templates. Below are some useful articles related to VBA –