VBA Columns

Excel VBA Columns Property

We all are well aware of the fact that an Excel Worksheet is arranged in columns and rows and each intersection of rows and columns is considered as a cell. Whenever we want to refer a cell in Excel through VBA, we can use the Range or Cells properties. What if we want to refer the columns from Excel worksheet? Is there any function which we can use to refer the same? The answer is a big YES!

Yes, there is a property in VBA called “Columns” which helps you in referring as well as returning the column from given Excel Worksheet. We can refer any column from the worksheet using this property and can manipulate the same.

Syntax of VBA Columns:

The syntax for VBA Columns property is as shown below:

Where,

• RowIndex – Represents the row number from which the cells have to be retrieved.
• ColumnIndex – Represents the column number which is in an intersection with the respective rows and cells.

Obviously, which column needs to be included/used for further proceedings is being used by these two arguments. Both are optional and if not provided by default would be considered as the first row and first column.

How to Use Columns Property in Excel VBA?

Below are the different examples to use columns property in excel using VBA code.

Example #1 – Select Column using VBA Columns Property

We will see how a column can be selected from a worksheet using VBA Columns property. For this, follow the below steps:

Step 1: Insert a new module under Visual Basic Editor (VBE) where you can write the block of codes. Click on Insert tab and select Module in VBA pane.

Step 2: Define a new sub-procedure which can hold the macro you are about to write.

Code:

Sub Example_1()

End Sub

Step 3: Use Columns.Select property from VBA to select the first column from your worksheet. This actually has different ways, you can use Columns(1).Select initially. See the screenshot below:

Code:

Sub Example_1()

Columns(1).Select

End Sub

The Columns property in this small piece of code specifies the column number and Select property allows the VBA to select the column. Therefore in this code, Column 1 is selected based on the given inputs.

Step 4: Hit F5 or click on the Run button to run this code and see the output. You can see that column 1 will be selected in your excel sheet.

This is one way to use columns property to select a column from a worksheet. We can also use the column names instead of column numbers in the code. Below code also gives the same result.

Code:

Sub Example_1()

Columns("A").Select

End Sub

Example #2 – VBA Columns as a Worksheet Function

If we are using the Columns property without any qualifier, then it will only work on all the Active worksheets present in a workbook. However, in order to make the code more secure, we can use the worksheet qualifier with columns and make our code more secure. Follow the steps below:

Step 1: Define a new sub-procedure which can hold the macro under the module.

Code:

Sub Example_2()

End Sub

Now we are going to use Worksheets.Columns property to select a column from a specified worksheet.

Step 2: Start typing the Worksheets qualifier under given macro. This qualifier needs the name of the worksheet, specify the sheet name as “Example 2” (Don’t forget to add the parentheses). This will allow the system to access the worksheet named Example 2 from the current workbook.

Code:

Sub Example_2()

Worksheets("Example 2")

End Sub

Step 3: Now use Columns property which will allow you to perform different column operations on a selected worksheet. I will choose the 4^th column. I either can choose it by writing the index as 4 or specifying the column alphabet which is “D”.

Code:

Sub Example_2()

Worksheets("Example 2").Columns("D")

End Sub

As of here, we have selected a worksheet named Example 2 and accessed the column D from it. Now, we need to perform some operations on the column accessed.

Step 4: Use Select property after Columns to select the column specified in the current worksheet.

Code:

Sub Example_2()

Worksheets("Example 2").Columns("D").Select

End Sub

Step 5: Run the code by pressing the F5 key or by clicking on Play Button.

Example #3 – VBA Columns Property to Select Range of Cells

Suppose we want to select the range of cells across different columns. We can combine the Range as well as Columns property to do so. Follow the steps below:

Suppose we have our data spread across B1 to D4 in the worksheet as shown below:

Step 1: Define a new sub-procedure to hold a macro.

Code:

Sub Example_3()

End Sub

Step 2: Use the Worksheets qualifier to be able to access the worksheet named “Example 3” where we have the data shown in the above screenshot.

Code:

Sub Example_3()

Worksheets("Example 3")

End Sub

Step 3: Use Range property to set the range for this code from B1 to D4. Use the following code Range(“B1:D4”) for the same.

Code:

Sub Example_3()

Worksheets("Example 3").Range("B1:D4")

End Sub

Step 4: Use Columns property to access the second column from the selection. Use code as Columns(2) in order to access the second column from the accessed range.

Code:

Sub Example_3()

Worksheets("Example 3").Range("B1:D4").Columns(2)

End Sub

Step 5: Now, the most important part. We have accessed the worksheet, range, and column. However, in order to select the accessed content, we need to use Select property in VBA. See the screenshot below for the code layout.

Code:

Sub Example_3()

Worksheets("Example 3").Range("B1:D4").Columns(2).Select

End Sub

Step 6: Run this code by hitting F5 or Run button and see the output.

You can see the code has selected Column C from the excel worksheet though you have put the column value as 2 (which means the second column). The reason for this is, we have chosen the range as B1:D4 in this code. Which consists of three columns B, C, D. At the time of execution column B is considered as first column, C as second and D as the third column instead of their actual positionings. The range function has reduced the scope for this function for B1:D4 only.

Things to Remember

• We can’t see the IntelliSense list of properties when we are working on VBA Columns.
• This property is categorized under Worksheet property in VBA.

Recommended Articles

This is a guide to VBA Columns. Here we discuss how to use columns property in Excel by using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA Insert Column
2. Grouping Columns in Excel
3. VBA Delete Column
4. Switching Columns in Excel