How to Count Columns with Data Using Excel VBA (2 Methods)

Method 1 – Count All the Columns with Data in a Worksheet

• Open the VBA editor by pressing ALT + F11.
• Create a new module from Insert > Module.

• Copy the following VBA script into the module:

Public Sub CountUsedColumns()
With Sheet1.UsedRange
MsgBox "The number of columns with Data is: " & .Columns.Count
End With
End Sub

• Paste the code in the VBA editor (CTRL + V).
• Save the VBA code (CTRL + S).

• To run the code, go to the Developer tab and click on Macros, or simply press F5.

• A dialog box will appear showing the number of columns with data (which is 3 in our case).

Method 2 – Count All the Columns in a Given Range with Data

• Open the VBA editor (ALT + F11).
• Create a new module (Insert > Module).

• Copy the following VBA script:

Sub CountColumnsInARange()
Dim xRng As Worksheet
Set xRng = Worksheets("Sheet1")
MsgBox "Total column: " & xRng.Range("B5:D5").Columns.Count
End Sub

• Paste the code (CTRL + V).
• Save it (CTRL + S).

• Go to the Developer tab and click on Macros or press F5.
• Choose the function name CountColumnsInARange and run it.

The dialog box will display the total number of columns (which is 3).

Additional VBA Codes to Count Columns in Excel

1. Use VBA Range.End Method to Return the Last Used Column Number

• Open the VBA editor (ALT + F11).
• Create a new module (Insert > Module).

• Copy the following VBA code:

Option Explicit
Sub LastColumn()
Dim xRng As Integer
xRng = Range("B4").End(xlToRight).Column
MsgBox xRng
End Sub

• Paste the code (CTRL + V).
• Save it (CTRL + S).

• Press the F5 key to run the above code.

You will get the last column number in a pop-up dialog box as in the picture below:

2. Use Range.Find Method to Return the Last Used Column Number in Excel

• Open the VBA editor (ALT + F11).
• Create a new module (Insert > Module).

• Copy the following VBA code:

Sub LastUsedColumnNo()
Dim xRng As Long
    xRng = Cells.Find(What:="*", _
                    After:=Range("B4"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
    MsgBox "Last Used Column Numeber: " & xRng
End Sub

• Paste the code (CTRL + V).
• Save it (CTRL + S).

• Press the F5 key to run the above code.

You will get the last used column number in a pop-up dialog box as in the picture below:

Things to Remember

• Press ALT + F11 to open the VBA editor.
• You can press ALT + F8 to access the Macros dialog box.
• To run the VBA code, press the F5.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Count Columns | Formula List | Learn Excel