[Excel VBA] Count Columns & Rows Having Data (10 Editable Codes)

When we have a very large data table, it seems daunting to count all the non-blank rows and columns manually. But the 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.

A. Count All Non-Blank Columns

1. Count All Columns Having Data in an Excel Sheet Using VBA

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.

🔗 A Complete Guideline to Insert and Run VBA Code in Excel

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.

🔴 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

2. Count All Columns in a Range Using VBA

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 🡻

🔗 2 Ways to Count Columns in Excel for Vlookup

3. Get the Last Non-Blank Column Number in an Excel Sheet Using VBA

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

B. Count All Non-Blank Rows

1. Count All Non-Empty Rows in a Range Using VBA

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 🡻

🔗 Count Rows in Excel Until a Certain Value Reached [2 Cases]

2. Count All Number of Rows in a Selected Range Using VBA

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

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.

🔗 3 Methods to Convert Column Number to Letter in Excel

4. Count All 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

5. Count All Non-Blank Cells 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 🡻

6. Count All 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 🡻

C. 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.

But this code can count all the rows and columns in an Excel sheet simultaneously.

🔗 A Complete Guideline to Insert and Run VBA Code in Excel

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!