How to Count Columns Using VBA

You can count columns using VBA in Excel in various scenarios. For instance, in data analysis, you may need to count the number of columns with a specific header value in a given range.

In this article, I will cover some examples of counting columns using VBA in Excel.

Scenario #1: Count Columns in a Specific Range

Suppose you want to determine the number of columns in the range A1:D10.

You can determine the number of columns within a specific range using the following code, which you can adapt to your requirements:

Sub Example1()

    ' Declare a Range variable to hold the target range
    Dim myRange As Range
    
    ' Declare an Integer variable to store the number of columns
    Dim columnCount As Integer
    
    ' Set the target range on Sheet1 from A1 to D10
    Set myRange = ThisWorkbook.Sheets("Sheet1").Range("A1:D10")
    
    ' Count the number of columns in the target range and store in columnCount
    columnCount = myRange.Columns.Count
    
    ' Display the number of columns in a message box
    MsgBox "The number of columns in the specified range is: " & columnCount

End Sub

The code initializes a range, myRange, to cover cells from A1 to D10 on “Sheet1” of the current workbook.

It then counts the number of columns in this range using the Columns.Count property and stores this count in an integer variable, columnCount.

Finally, a message box displays the count of the columns in the specified range.

Scenario #2: Count Columns in Used Range

The “used range” in an Excel worksheet refers to the portion containing data or formatting, including all cells used at least once.

You can use the following code to count columns in a used range on a specified worksheet:

Sub Example2()

    ' Declare a Worksheet variable to hold the target worksheet
    Dim ws As Worksheet
    
    ' Declare a Range variable to hold the used range of the worksheet
    Dim usedRange As Range
    
    ' Declare an Integer variable to store the number of columns
    Dim columnCount As Integer
    
    ' Set the target worksheet to "Sheet1" of the current workbook
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Set the used range of the worksheet to the usedRange variable
    Set usedRange = ws.UsedRange
    
    ' Count the number of columns in the used range and store in columnCount
    columnCount = usedRange.Columns.Count
    
    ' Display the number of columns in a message box
    MsgBox "The number of columns in the used range is: " & columnCount

End Sub

The code first identifies the worksheet (“Sheet1”) you’re interested in and sets that to the ws variable.

It then identifies the “used range” of that worksheet using the UsedRange property and stores it in the usedRange variable.

The Columns.Count property is then used to count the number of columns in this used range, and this count is stored in the columnCount variable. Finally, a message box displays this count.

Also read: Delete Blank Rows Using VBA

Scenario #3: Count Non-Empty Columns in a Used Range

The “used range” in an Excel worksheet refers to the part of the worksheet containing data or formatting, including all cells used at least once.

You can use the following code to determine the count of non-empty columns in a used range:

Sub Example3()

    ' Declare a Worksheet variable to hold the worksheet
    Dim ws As Worksheet
    
    ' Declare a Range variable to hold the used range
    Dim usedRange As Range
    
    ' Declare a Range variable to iterate through each column
    Dim column As Range
    
    ' Declare an Integer variable to hold the count of non-empty columns
    Dim nonEmptyColumnCount As Integer
    
    ' Set the target worksheet and used range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set usedRange = ws.UsedRange
    
    ' Initialize the count to 0
    nonEmptyColumnCount = 0
    
    ' Loop through each column in the used range
    For Each column In usedRange.Columns
        
        ' If the column has at least one non-empty cell, increment the count
        If Application.WorksheetFunction.CountA(column) > 0 Then
            nonEmptyColumnCount = nonEmptyColumnCount + 1
        End If
        
    Next column
    
    ' Display the count of non-empty columns
    MsgBox "The number of non-empty columns in the used range is: " & nonEmptyColumnCount

End Sub

The code uses the ‘For Each Next’ loop to go through every column in the worksheet’s used range.

During each iteration, it checks for cells that are not empty using the ‘CountA’ function. Every column with at least one non-empty cell is added to the ‘nonEmptyColumnCount’ variable.

After the loop completes, the ‘nonEmptyColumnCount’ variable holds the number of non-empty columns within the used range. This value is then displayed in a message box.

Scenario #4: Count Empty Columns In a Used Range

The “used range” in an Excel worksheet refers to the part containing data or formatting, including all cells ever used.

You can use the following code to count empty columns within a used range:

Sub Example4()

    ' Declare a Range variable to hold the used range
    Dim usedRange As Range
    
    ' Declare a Range variable to iterate through each column
    Dim column As Range
    
    ' Declare an Integer variable to hold the count of empty columns
    Dim emptyColumnCount As Integer
    
    ' Set the used range of the target worksheet
    Set usedRange = ThisWorkbook.Sheets("Sheet1").UsedRange
    
    ' Initialize the count to 0
    emptyColumnCount = 0
    
    ' Loop through each column in the used range
    For Each column In usedRange.Columns
        
        ' If the column has no non-empty cells, increment the count
        If Application.WorksheetFunction.CountA(column) = 0 Then
            emptyColumnCount = emptyColumnCount + 1
        End If
        
    Next column
    
    ' Display the count of empty columns
    MsgBox "The number of empty columns in the used range is: " & emptyColumnCount

End Sub

The code utilizes the ‘For Each Next’ loop to iterate through each column in the worksheet’s used range. At each iteration, it checks for non-empty cells using the ‘CountA’ function. A column with zero non-empty cells is added to the ’emptyColumnCount’ variable.

After the loop finishes, the ’emptyColumnCount’ variable contains the number of empty columns in the used range. This value is then shown in a message box.

Scenario #5: Count Columns in the Current Region

The current region is typically a block of data surrounding a specific cell, bounded by any combination of blank rows and columns.

You can apply the following code in counting columns in the current region around the active cell on a worksheet:

Sub Example5()

    ' Declare a Range variable to hold the current region range
    Dim currentRegionRange As Range
    
    ' Declare an Integer variable to hold the count of columns
    Dim columnCount As Integer
    
    ' Set the current region range based on the current selection
    Set currentRegionRange = Selection.CurrentRegion
    
    ' Get the column count of the current region
    columnCount = currentRegionRange.Columns.Count
    
    ' Display the column count in a message box
    MsgBox "Number of Columns in the current region: " & columnCount

End Sub

The code utilizes the ‘Count’ property of the ‘currentRegionRange.Columns’ collection to determine the number of columns in the current region of the selected cell and displays that count in a message box.

Scenario #6: Count Hidden Columns in a Specific Range

Suppose you want to determine the number of hidden columns in the cell range A1:D10.

You can determine the number of hidden columns within the specified range using the following code, which you can adapt to your requirements:

Sub Example6()

    ' Declare a Range variable to hold the target range
    Dim targetRange As Range
    
    ' Declare an Integer variable to hold the count of hidden columns
    Dim hiddenColumnCount As Integer
    
    ' Declare a Range variable for individual columns within the target range
    Dim column As Range
    
    ' Initialize the target range
    Set targetRange = ThisWorkbook.Sheets("Sheet1").Range("A1:D10")
    
    ' Initialize the hidden column count to zero
    hiddenColumnCount = 0
    
    ' Loop through each column in the target range
    For Each column In targetRange.Columns
        ' Check if the column is hidden
        If column.Hidden Then
            ' Increment the hidden column count
            hiddenColumnCount = hiddenColumnCount + 1
        End If
    Next column
    
    ' Display the hidden column count in a message box
    MsgBox "Number of Hidden Columns in the specified range: " & hiddenColumnCount

End Sub

The code utilizes the ‘For Each Next’ loop to iterate through each column in the specified range.

At each iteration, it checks if the column is hidden. If a column is hidden, it is added to the ‘hiddenColumnCount’ variable.

After the loop finishes, the ‘hiddenColumnCount’ variable contains the number of hidden columns in the specified range. This value is then shown in a message box.

Scenario #7: Count Hidden Columns in a Used Range

The “used range” in an Excel worksheet refers to the part containing data or formatting, including all cells ever used.

You can use the following code to count hidden columns within a used range:

Sub Example7()

    ' Declare a Range variable to hold the used range
    Dim usedRange As Range
    
    ' Declare an Integer variable to hold the count of hidden columns
    Dim hiddenColumnCount As Integer
    
    ' Declare a Range variable for individual columns within the used range
    Dim Col As Range
    
    ' Initialize the used range
    Set usedRange = ThisWorkbook.Sheets("Sheet1").UsedRange
    
    ' Initialize the hidden column count to zero
    hiddenColumnCount = 0
    
    ' Loop through each column in the used range
    For Each Col In usedRange.Columns
        ' Check if the column is hidden
        If Col.Hidden Then
            ' Increment the hidden column count
            hiddenColumnCount = hiddenColumnCount + 1
        End If
    Next Col
    
    ' Display the hidden column count in a message box
    MsgBox "Number of hidden columns in the used range: " & hiddenColumnCount

End Sub

The code utilizes the ‘For Each Next’ loop to iterate through each column in the used range.

At each iteration, it checks if the column is hidden. If a column is hidden, it is added to the ‘hiddenColumnCount’ variable.

After completion of the loop, the ‘hiddenColumnCount’ variable contains the number of hidden columns within the used range. This value is then displayed in a message box.

Scenario #8: Count Columns With a Specific Header Value in a Worksheet

You can use the following code to count columns on a worksheet with a specific header value: 

Sub Example8()

    ' Declare a Worksheet variable to hold the worksheet
    Dim ws As Worksheet
    
    ' Declare a String variable to hold the header text to look for
    Dim headerToCount As String
    
    ' Declare a Range variable for individual header cells in the first row
    Dim headerCell As Range
    
    ' Declare an Integer variable to hold the count of columns with the specified header
    Dim columnCount As Integer
    
    ' Initialize the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Initialize the header text to look for
    headerToCount = "2023"
    
    ' Initialize the column count to zero
    columnCount = 0
    
    ' Loop through each cell in the first row
    For Each headerCell In ws.Rows(1).Cells
        ' Check if the cell value matches the header text to look for
        If headerCell.Value = headerToCount Then
            ' Increment the column count
            columnCount = columnCount + 1
        End If
    Next headerCell
    
    ' Display the column count in a message box
    MsgBox "Number of columns with header '" & headerToCount & "': " & columnCount

End Sub

The code uses the ‘For Each Next’ loop to iterate through each cell in the first row of the worksheet. If the cell contains the specified header value, it increments the ‘columnCount’ variable by 1.

After completing the loop, the ‘columnCount’ variable contains the number of columns with the specified header value. The column count is then displayed in a message box.

Scenario #9: Count Columns in an Excel Table

Excel tables, or ‘ListObjects’ in VBA, are structured data ranges organized into rows and columns.

You can utilize the below code to count columns in an Excel table:

Sub Example9()

    ' Declare a Worksheet variable to hold the worksheet
    Dim ws As Worksheet
    
    ' Declare a ListObject variable to hold the table
    Dim tbl As ListObject
    
    ' Declare an Integer variable to hold the number of columns in the table
    Dim columnCount As Integer
    
    ' Initialize the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Initialize the table
    Set tbl = ws.ListObjects("Products")
    
    ' Count the number of columns in the table
    columnCount = tbl.ListColumns.Count
    
    ' Display the column count in a message box
    MsgBox "Number of columns in the table: " & columnCount

End Sub

The code uses the ‘Count’ property of the ‘tbl.ListColumns’ collection to determine the number of columns in the table. The value is then displayed in a message box. 

Scenario #10: Generate a Report of All Tables in a Workbook With Column Counts

You can generate a summary report that lists the names of all tables in the workbook, along with their respective column counts.

You can use the following code:

Sub Example10()

    ' Declare a Worksheet variable to loop through each worksheet in the workbook
    Dim ws As Worksheet
    
    ' Declare a ListObject variable to loop through each table in each worksheet
    Dim tbl As ListObject
    
    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets
        
        ' Loop through each table in the worksheet
        For Each tbl In ws.ListObjects
            
            ' Print the table name and its column count to the Immediate Window
            Debug.Print "Table '" & tbl.Name & "' has " & tbl.ListColumns.Count & " columns."
            
        Next tbl
        
    Next ws

End Sub

The code includes an outer ‘For Each Next’ loop that goes through all the worksheets in the workbook.

Additionally, an inner ‘For Each Next’ loop goes through all the tables in each worksheet. The code then displays the names of the tables and their respective column counts in the Immediate window.

In this tutorial, I showed you ten ways to count columns using VBA in Excel. I hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment