To count rows using VBA, you need to define the range from which you want to count the rows and then use the count and rows property to get the count of the row from that range. You can also use a loop to count rows where you have data only.
Use VBA to Count Rows
- First, you need to define the range for which you want to count the rows.
- After that, use a dot (.) to open the list of properties and methods.
- Next, type or select the “Rows” property.
- In the end, use the “Count” property.
Sub vba_count_rows() Range("A1:A10").Rows.Count End Sub
Count Rows for the Used Range
Sub vba_count_rows2() MsgBox Worksheets("Sheet1").UsedRange.Rows.Count End Sub
Count Rows with Data using VBA
You can also count rows where you have data by ignoring the blank rows.
The following code will take used range as the range to loop up at and loop through each row one by one and check if there’s a non-empty cell there, and if it is there it will consider it as a row with data, and in the end, show a message box with the total count of rows.
Sub vba_count_rows_with_data() Dim counter As Long Dim iRange As Range With ActiveSheet.UsedRange 'loop through each row from the used range For Each iRange In .Rows 'check if the row contains a cell with a value If Application.CountA(iRange) > 0 Then 'counts the number of rows non-empty Cells counter = counter + 1 End If Next End With MsgBox "Number of used rows is " & counter End Sub
If you are preparing for an interview, make sure to check out these VBA interview questions.