How to Count Columns in VBA

1. Count Columns in VBA
2. Count Column in Range in VBA
3. Use the Range().End Method in VBA
4. Use the Cells.Find() Method in VBA

This article will discuss two ways to easily count columns with data using VBA in Excel.

Count Columns in VBA

When we have a small data table with a few columns, we can easily count them, but it’s quite tough for a large data table to count all the columns without any error. Besides that, some columns may contain data, whereas some columns may be completely blank.

Thus counting all the columns with data in case of a large data table is quite difficult. Now, let’s create a sheet with some sample data to work with.

We can see the three columns in the following data table. To control the number of used columns in a single Excel worksheet, We will use VBA codes.

First, we open the VBA editor by pressing the ALT + F11 key. After that, create a new module from Insert > Module.

Then, create a new sub, usedColumns(). Inside our new sub, we will use a with loop to get the used range using the method UsedRange.

After that, we use the count method of columns to output the number of used columns.

Example Code:

# VBA
Sub usedColumns()
With Sheet1.UsedRange
MsgBox "The Used Columns are: "& .Columns.Count
End With
End Sub

Save the macro and run it by pressing F5 or clicking on the run. The Macro dialog box will appear as shown below.

Output:

Count Column in Range in VBA

The following VBA code counts all the columns with data in a given range.

Let’s create a new sub ColumnsInRange(). Inside this sub, we will use the range function to select and count the number of columns in that range.

Example Code:

# VBA
Sub ColumnsInRange()
Dim newRange As Worksheet
Set newRange = Worksheets("Sheet1")
MsgBox "The Used Columns are: " & newRange.Range("A15:D15").Columns.Count
End Sub

Output:

Use the Range().End Method in VBA

We can use the Range().End method to get the last column used in that range.

Create a new sub, findLastColumn(), and inside that sub, we will use the End method of range to find the last column used towards the right side of the sheet.

# VBA

Sub findLastColumn()
Dim newRange As Integer
newRange = Range("A2").End(xlToRight).Column
MsgBox newRange
End Sub

Output:

We get the last column number in a pop-up dialog box, as in the picture shown above.

Use the Cells.Find() Method in VBA

We can also use the Range.Find method to get the last used column from the sheet using the VBA code.

Check the following code. In the LastColumnByFind() sub, we use the Cells.Find() method to find the last used column.

Example Code:

# VBA
Sub LastColumnByFind()
Dim newRange As Long
    newRange = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
    MsgBox "Last Used Column Number by Find Method: " & newRange
End Sub

Output: