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.

Count Rows with Data Using Excel VBA in a Whole Sheet

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

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


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.

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

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

Determine Used Rows Using Excel VBA for One Column

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

Determine Used Rows Using Excel VBA for One Column

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

Determine Used Rows Using Excel VBA for One Column

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
  • Run the codes, press the play icon.

Run Excel VBA Code to Calculate Used Rows with Specific Word

  • Select the Macro name which is specified in the codes.
  • Press Run.

Run Excel VBA Code to Calculate Used Rows with Specific Word

A pop-up message box will then show the result.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Related Article

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo