WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Columns

VBA Columns

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA Columns Property

VBA 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.

When we want to refer to the cell, we use either the Range object or Cells property. Similarly, how do you refer to columns in VBA? We can refer to columns by using the “Columns” property. Look at the syntax of COLUMNS property.

Columns Property

We need to mention the column number or header alphabet to reference the column.

For example, if we want to refer to the second column, we can write the code in three ways.

Columns (2)

Columns(“B:B”)

Range (“B:B”)

Examples

You can download this VBA Columns Excel Template here – VBA Columns Excel Template

Example #1

If you want to select the second column in the worksheet, then first, we need to mention the column number we need to select.

Code:

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.

Code:

Sub Columns_Example()

  Columns(2).Select

End Sub

So, the above VBA code will select the second column of the worksheet.

VBA Columns Example

Instead of mentioning the column number, we can also use the column header alphabet “B” to select the second column.

Code:

Sub Columns_Example()

  Columns("B").Select
  Columns("B:B").Select

End Sub

Both the above codes will select column B, i.e., second column.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Example #2 – Select Column Based on Variable Value

We can also use the variable to select the column number. Look at the below code now.

Code:

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 the Column’s property, I have supplied this variable instead of the column number. Since the variable holds the value of 4, it will select the 4th column.

Example #3 – 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.

VBA Columns Example 1

Now below code will select the column based on the number in the cell A1.

Code:

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 #4 – Combination of Range & Column Property

We can also use Columns property with Range object as well. Using the Range object, we can specify the specific range. For example, look at the below code.

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 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.

Example 2

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 COLUMNS property, I have specified the range by using the RANGE object as C1 to D5. Now 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 #5 – Select Multiple Columns with Range Object

Using the Range object and Columns property, we can select multiple columns. Look at the below code.

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.

Example 3

We can also write the code in this way as well.

Code:

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 to work with the worksheet.

Recommended Articles

This has been a guide to VBA Columns. Here we discuss examples of columns property in excel VBA and select multiple columns with range object and downloadable excel templates. Below are some useful articles related to VBA –

  • DateSerial Function in Excel VBA
  • Hide Columns in VBA
  • Insert Columns in VBA
  • Delete Column in VBA
  • VBA Variable Types
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Columns Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More