VBA: How to Count Number of Used Columns


You can use the following basic syntax to count the number of used columns in an Excel sheet using VBA:

Sub CountColumns()
    Range("A10") = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
End Sub

This particular example counts the number of used columns in the sheet called Sheet1 and displays the result in cell A10.

If you would instead like to display the column count in a message box, you can use the following syntax:

Sub CountColumns()
    Dim LastCol As Long
    LastCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
    MsgBox "Column Count: " & LastCol
End Sub

The following examples shows how to use each of these methods in practice with the following dataset in Excel that contains information about various basketball players:

Example 1: Count Used Columns in VBA and Display Results in Cell

Suppose we would like to count the number of used columns and output the results in a specific cell.

We can create the following macro to do so:

Sub CountColumns()
    Range("A10") = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
End Sub

When we run this macro, we receive the following output:

Notice that cell A10 contains a value of 4.

This tells us that the there are 4 used columns.

Example 2: Count Used Columns in VBA and Display Results in Message Box

Suppose we would instead like to count the number of used columns and output the results in a message box.

We can create the following macro to do so:

Sub CountColumns()
    Dim LastCol As Long
    LastCol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
    MsgBox "Column Count: " & LastCol
End Sub

When we run this macro, we receive the following output:

VBA count number of used columns

The message box tells us that there are 4 used columns.

Note: If there are empty columns followed by columns with data, VBA will count the empty columns as “used” in the calculation.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Count Number of Sheets in Workbook
VBA: How to Count Number of Rows in Range
VBA: How to Count Cells with Specific Text

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *