Excel VBA to Count Rows with Data: 4 Methods

Method 1 – Count Rows with Data Using Excel VBA in a Whole Sheet

Steps:

Right-click on the sheet title.
Select View Code from the context menu.

Soon after a VBA window will open up.

Type the codes given below-

Sub count_rows_with_data_wholeSheet()
Dim x As Long
Dim y As Range
With ActiveSheet.UsedRange
    For Each y In .Rows
        If Application.CountA(y) > 0 Then
            x = x + 1
        End If
    Next
End With
MsgBox "Number of rows with data is " & x
End Sub

Press the play icon to run the codes.

Then you will get the output with a pop-up message box.

Method 2 – Embed VBA Code to Count Rows with Data for a Range Contains Continuous Data

Steps:

Select your continuous data range.
Right-click on your sheet title.
Select View Code from the context menu.

After the VBA window appears, write the following codes in it-

Sub CountUsedRows()
Dim x As Long
x = Selection.Rows.Count
MsgBox x & " rows with data in the selection"
End Sub

Press the Play icon to run the codes.

See that a message box is showing the counted rows.

Method 3 – Determine Used Rows Using Excel VBA for One Column

Steps:

Select data range from the specific column.
Right-click on that sheet title.
Select View Code from the context menu.

A VBA window for that sheet will open up.

Type the following codes-

Sub CountRows_with_Data_in_a_Column()
Dim Total_Rows As Long
Dim Blank_Rows As Long
Set Rng = Selection
Total_Rows = Rng.Rows.Count
Blank_Rows = Application.WorksheetFunction.CountBlank(Rng)
MsgBox "The Number of Used Rows is: " + Str(Total_Rows - Blank_Rows)
End Sub

Click the play icon to run the codes.

A dialog box named ‘Macros’ will appear.

Select the Macro name that we used in the codes.
Press Run.

Get the output like this in a message box.

Method 4 – Run Excel VBA Code to Calculate Used Rows with Specific Word

Steps:

Select data range from the column where the specific word exists.
Right-click on that sheet title.
Click View Code from the context menu.

After opening the VBA window, type the following codes-

Sub Count_Rows_with_SpecificWord()
Dim counter As Long
counter = 0
Dim Rng As Range
Set Rng = Selection
For i = 1 To Rng.Rows.Count
        If Rng.Cells(i, 1).Value = 15 Then
            counter = counter + 1
        End If
Next i
MsgBox "Number of rows with specific word: " + Str(counter)
End Sub