# Excel VBA to Count Rows with Data (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

We can count rows with data by using workbook functions or conditional formatting but if we work with a large amount of data then using VBA (Visual Basic for Applications) is much more effective and time-saving. So, this article will help you with 4 VBA Macros to count rows with data in Excel including clear steps and vivid illustrations.

## Excel VBA to Count Rows with Data: 4 Examples

For applying VBA Macros I have made a dataset that contains some studentsâ€™ names, IDs, and ages.

### 1. Count Rows with Data Using Excel VBA in a Whole Sheet

In this first example, Iâ€™ll show how to count used rows in a whole worksheet using VBA codes which means it will count all the rows with any data anywhere in the worksheet.

Steps:

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

Soon after a VBA window will open up.

• Then 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``````
• Later, press the play icon to run the codes.

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

Read More: Excel VBA: Count Rows in a Sheet

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

Now Iâ€™ll show how to apply VBA to count rows with data if you want to count them from a selection of continuous data. Itâ€™s very helpful for some particular moments.

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``````
• Finally, just press the Play icon to run the codes.

Now see that a message box is showing the counted rows.

### 3. Determine Used Rows Using Excel VBA for One Column

If we need to calculate rows with data for a specific column, then it is also possible to do so using Excel VBA. I have removed a row from the dataset. Now, Iâ€™ll use VBA to count the used rows in column B.

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.
• Then press Run.

Soon after you will get the output like this in a message box.

### 4. Run Excel VBA Code to Calculate Used Rows with Specific Word

In our last example, Iâ€™ll explain the procedure to count used rows with a specific word which means it will count only those rows that contain our selected specific word. Here Iâ€™ll use VBA to count the rows that contain the specific word â€˜15â€™, Letâ€™s go forward.

Steps:

• Select data range from the column where the specific word exists.
• Then 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``````
• Now to run the codes, press the play icon.

• After that select the Macro name which is specified in the codes.
• Later, just press Run.

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

## Conclusion

I hope all of the methods described above will be good enough to count rows with data using VBA in Excel. Feel free to ask any question in the comment section and please give me feedback.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF