How to Count Columns & Rows with data [10 Excel VBA Codes]

When we have a very large data table, it seems daunting to count all the non-blank rows and columns manually. However, VBA scripts can make this task very easy. Here, you will get 10 editable VBA codes to count all the columns and rows having data in an Excel sheet.

I’ve used the dataset below to show how to count columns as well as rows with data using VBA scripts in Excel.

Count All Non-Blank Columns with Data in Excel

Counting all non-blank columns with data in Excel is a crucial task for effective data analysis and reporting. This process allows you to quickly assess the depth and breadth of information within your spreadsheet. So,  I will mention different cases to count all non-blank columns with Data.

Case 1: Use VBA Code in an Excel Sheet 

This piece of VBA code can count all the columns having data in an Excel sheet. Just insert the code inside a new Module in your Visual Basic Editor and Run it.

Sub Count_Columns_in_a_Sheet()

With Sheet1.UsedRange
MsgBox .Columns.Count & " Columns!"
End With

End Sub

Editable Lines 

With Sheet1.UsedRange

In this line insert your sheet name in the place of Sheet1.

After running the code, a little dialog box shows “4 Columns!”, which is the exact column count number in my Excel sheet.

Note: One crucial perspective you should know. If you have completely blank columns followed by non-blank columns, the code still counts those blank columns in.

• 3 Ways to Find Column Index Number in Excel
• A Complete Guideline to Insert and Run VBA Code in Excel

Case 2: Apply VBA Code in a Range 

This code will count all the columns in a given range mentioned in the code. This code counts all the blank as well as non-blank columns within the specified range.

Sub Count_All_Columns_in_a_Range()

Dim gRng As Worksheet

Set gRng = Worksheets("Sheet1")
MsgBox gRng.Range("A1:D12").Columns.Count & " Columns!"

End Sub

Editable Lines 

Set gRng = Worksheets("Sheet1")

In this line, just insert your sheet name in replace of Sheet1.

MsgBox gRng.Range("A1:D12").Columns.Count & " Columns!"

Here, you need to update the range only inside the Range function.

Insert the above code in your Visual Basic Editor and Run.

Code Output 

Case 3: Use VBA to get Last Non-Blank Column Number in Excel Sheet 

An interesting way to know the total number of columns having data is to get the last used column number. This piece of code exactly does this task for you.

Sub Last_Non_Blank_Column_Number()
Dim LastNonBlankColumn As Long

LastNonBlankColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "Last non-blank column number is: " & LastNonBlankColumn

End Sub

Editable Lines 

LastNonBlankColumn = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column

In this line, just update the sheet name replacing Sheet1.

Code Output 

• [2 Cases] Count Columns in Excel Until a Certain Value Reached
• 2 Ways to Count Columns in Excel for Vlookup

Count All Non-Blank Rows in Excel

Counting all non-blank rows with data in Excel is a fundamental aspect of efficient data analysis and management. Rather than manually examining each row, leveraging Excel functions allows for an automated and dynamic approach to counting non-blank rows. Now, I will add codes for different cases to count all non-blank rows in Excel.

Case 1: Apply a VBA Code in a Range 

This code counts all the non-blank rows in a given range specified in the code.

Sub Count_Non_Empty_Rows_in_a_Range()

Dim NonEmptyRows As Integer

NonEmptyRows = Range("A1:A12").Rows.Count
MsgBox "Total " & NonEmptyRows & " used rows!"

End Sub

Editable Lines 

NonEmptyRows = Range("A1:A12").Rows.Count

Just update the cell ranges inside the Range function here.

Insert the code insert a new Module of your Visual Basic Editor and Run.

Code Output 

Case 2: Use a VBA code in a Selected Range 

To use this code, you have to select a range of cells first. Then you can run the VBA code and it will work perfectly.

What this code actually does is that it counts all the number of rows inside the selected range. It counts all the blank as well as non-blank rows within the selected area.

Sub Count_Used_Rows_in_Selected_Range()

Dim gRows As Integer

gRows = Selection.Rows.Count
MsgBox "Total " & gRows & " rows in the selected range!"

End Sub

Insert the above code in a new Module in your Visual Basic Editor. Then press F5 to Run the code.

Code Output 

• 6 Ways to Count One Column in Excel If Another Column Meets Criteria
• Count Rows in Excel Until a Certain Value Reached [2 Cases]
• A Complete Guideline to Insert and Run VBA Code in Excel

Case 3: Count All Non-Blank Rows in a Given Range Using VBA

This code can count only the non-blank rows in a given range. It completely ignores all the blank rows while counting.

Sub Count_Non_Blank_Rows_in_Range()

Dim gNum As Long
Dim pRng As Range
Dim gRng As Range

Set gRng = Range("B1:B12")
With gRng
For Each pRng In .Rows
If Application.CountA(pRng) > 0 Then
gNum = gNum + 1
End If
Next
End With
MsgBox "Total " & gNum & " number of non-empty rows!"

End Sub

Editable Lines 

Set gRng = Range("B1:B12")

Just update the range inside the Range function and then you are ready to run the code.

Code Output 

Here the given range is B1:B12. In this range, there is a total of 11 rows. But two rows are completely blank in this range. Thus the code counts 9 rows as non-empty.

Case 4: Write a VBA Code to Count Rows Having Specific Data in a Given Range

This code can count all the rows having specific data in a given range. The data can be anything such as text, number, etc.

For this instance, this code will count all the rows having $ 376,533 in the range A1:A12.

Sub Count_Rows_with_Specific_Data_in_Range()

Dim gNum As Long
Dim pRng As Range
Dim gRng As Range

Set gRng = Range("A1:A12")
With gRng
For Each pRng In .Rows
If Application.CountIf(pRng, 376533) > 0 Then
gNum = gNum + 1
End If
Next
End With
MsgBox "Total " & gNum & " number rows having  $ 376,533!"

End Sub

Editable Lines 

Set gRng = Range("A1:A12")

In this line, just update the range inside the Range function.

If Application.CountIf(pRng, 376533) > 0 Then

Update the number inside the Countif function. This code will count all the rows having your input number in the Countif function within your given range.

MsgBox "Total " & gNum & " number rows having $ 376,533!"

Update the number based on which you want to count rows inside the double inverted comma (“”).

Code Output 

Suppose, you want to count all the rows having values more than 376533. In such a case, just use the following line inside the main code.

If Application.CountIf(pRng, ">376533") > 0 Then

To count all the rows having a specific text, use the following line of code inside the main code.

If Application.CountIf(pRng, "*Text*") > 0 Then

Case 5: Add a VBA Code in a Selected Column

Just click on any cell of any column to select the cell. Next, run this code. It will instantly return the number of all non-blank cells in the selected column.

Sub Count_Non_Empty_Cells_in_a_Column()

Dim gCol As Integer, gRng As Range, n#, b#

gCol = Selection.Column
If Application.WorksheetFunction.CountA(Columns(gCol)) = 0 Then
MsgBox "Your selected column is Blank!"
n = 0
Else
Set gRng = Intersect(Columns(gCol), ActiveSheet.UsedRange)
On Error Resume Next
b = gRng.Cells.SpecialCells(xlCellTypeBlanks).Count
n = gRng.Cells.Count - b
On Error GoTo 0
MsgBox "Column " & gCol & " has total " & n & " non-empty cells!"
End If

End Sub

To run the code,

• Press ALT + F11. This will open the Visual Basic Editor.
• Click on the Insert tab. Then choose Module. This will create a new Module.
• Insert the VBA code in the newly created module.
• Save it.
• Then press F5. This command will run the code.

Code Output 

Case 6: Use VBA Code to Count Used Rows in an Active Excel Sheet

In this code, there’s no range to insert. Just run this code and it will instantly return the total number of all used rows in the active Excel sheet.

Sub Count_Used_Rows_in_a_Sheet()

With ActiveSheet.UsedRange
MsgBox .Rows.Count & " used rows!"
End With

End Sub

Just open the Visual Basic Editor and insert the above code in a new Module. Then press the F5 key to run the code.

Code Output 

Count All Non-Blank Rows & Columns at the Same Time Using VBA in Excel

All the above codes can either count rows or columns separately. Nevertheless, this code can count all the rows and columns in an Excel sheet simultaneously.

Sub Count_Rows_and_Columns()

With ActiveSheet.UsedRange
MsgBox "Total " & .Columns.Count & " columns & " & .Rows.Count & " rows!"
End With

End Sub

To run the code, insert it into a Module on Visual Basic Editor. Then click on the Run Sub button or press the F5 key.

Code Output 

Conclusion

I’ve discussed 10 editable codes that you can use to count rows and columns having data in an Excel sheet. You are invited to drop your valuable opinion in the comment section below. Thanks!

Frequently Asked Questions

What is the maximum row count in Excel VBA?

In Excel VBA, the maximum row count is determined by the version of Excel you are using. As of my knowledge cutoff date in January 2022, Excel 2016 and later versions support a maximum of 1,048,576 rows. This limitation is due to the structure of Excel’s grid, which consists of 2^20 rows (from row 1 to 1,048,576) and 2^14 columns.

It’s important to note that Excel 2003 and earlier versions had a maximum of 65,536 rows. However, with the introduction of Excel 2007 and subsequent versions, the row limit was significantly increased to accommodate larger data sets.

Keep in mind that software updates may have occurred since my last knowledge update, so it’s advisable to check the latest documentation or resources for any potential changes in Excel’s row limits.

What is column count in VBA?

In VBA, the column count in Excel is determined by the ‘Columns.Count’ property. This property provides the total number of columns in a worksheet. For example, to retrieve the column count in a specific worksheet, you can use ‘ActiveSheet.Columns.Count.’ This information is crucial when working with dynamic data ranges or creating flexible VBA code that adapts to the size of the dataset.

How do you select all columns with data in VBA?

To select all columns with data in VBA, find the last used column using the ‘End’ property and then select the entire range. Use the following code as an example:

Sub SelectAllColumnsWithData()
Dim LastColumn As Long

' Find the last used column in the active sheet
LastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

' Select the range from the first to the last column with data
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(ActiveSheet.UsedRange.Rows.Count, LastColumn)).Select
End Sub

This VBA code dynamically determines the last used column and selects all columns with data in the active sheet. Adjust the sheet reference as needed for your specific workbook.