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 SubThe 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.
Also read: VBA to AutoFit Column Width
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:
