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. This can be a single cells, a range of cells, or a non-continues range.
- After that, use a dot (.) to open the list of properties and methods. This has all the properties and method which you can use with the object.
- Next, from the list that appears select the “Rows” property. Or you can also type the “Rows” directly in the code.
- In the end, enter a dot and then you will again get a list of properties and methods to select. Select the “Count” property or you can type it too.
Sub vba_count_rows()
Range("A1:A10").Rows.Count
End SubNow when you run this code, it will return the count of the rows, and to get the count you can use a message box or directly enter that value into a cell as well.
Rows.Count is a property that returns a Long value representing the total number of rows in a specified range or object. When used without any reference of a range of a Worksheet object, it returns the total number of rows available in the worksheet. For example, in Excel versions from 2007 onward, this number is 1,048,576 rows, which is the maximum number of rows available.
Count Rows for the Used Range
It displays a message box that shows the count of used rows in the worksheet “Sheet1”. The UsedRange property refers to the area of the worksheet currently in use, meaning it includes all cells that have ever been used (i.e., had data entered into them) in the worksheet. The Rows.Count part of the command counts the number of rows in the used range.
Dim usedRowCount As Long usedRowCount = ActiveSheet.UsedRange.Rows.Count MsgBox "Number of used rows: " & usedRowCount
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 the 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
It declares two variables: counter to keep track of the number of rows with data and iRange to represent each row in the used range of the active sheet. It loops through each row in the used range of the active sheet (ActiveSheet.UsedRange).
For each row, it uses the Application.CountA function to count the number of cells with a value. If this count is greater than 0 (i.e., if there’s at least one cell with data), it increments the counter. After all rows have been checked, a message box (MsgBox) will appear showing the number of rows with data (counter).
Count Non-Empty Rows in a Specific Column
This VBA code calculates and displays the number of non-empty rows in column A of the active sheet in Excel.
Dim rowCount As Long
rowCount = Application.CountA(ActiveSheet.Range("A:A"))
MsgBox "Non-empty rows in column A: " & rowCount - Dim rowCount As Long – This line declares a variable named
rowCountof typeLong. This variable will store the count of non-empty rows. - rowCount = Application. CountA(ActiveSheet.Range(“A:A”)): This line assigns the count of non-empty cells in column A of the active sheet to the variable
rowCount. CountA()` is an Excel function that returns the number of non-empty cells in a range. - MsgBox “Non-empty rows in column A: ” & rowCount: This line displays a message box that shows the number of non-empty rows in column A. The
&operator concatenates the stringNon-empty rows in column A: "with the value ofrowCount.
Counting rows in a range (including empty rows)
If you need the number of rows in a specific range, you can use this:
Dim rowCount As Long
rowCount = ActiveSheet.Range("A1:A100").Rows.Count
MsgBox "Rows in range A1:A100: " & rowCount ActiveSheet refers to the sheet currently selected or active in the Excel workbook. Range(“A1:A100”) specifies a range of cells in the active sheet; here, it is between cell A1 and A100. Rows.Count then counts the number of rows within this specified range.
Count Rows with a VBA UDF
You can use a simple way to create a VBA function that counts the number of rows in a specified range within an Excel worksheet. The below function allows you to specify a range as input and return the number of rows in that range.
Function iCountRows(rng As Range) As Long
' This function returns the number of rows in the provided range object.
' Return the count of rows in the specified range.
iCountRows = rng.Rows.Count
End Function Count Only the Visible Rows
The below VBA function iterates through each row in a specified Excel range and counts how many are visible. It returns the total number of visible rows.
Function CountVisibleRows(rng As Range) As Long
' This function counts the number of visible rows within a given range.
Dim row As Range
Dim visibleRowCount As Long
' Initialize the count of visible rows
visibleRowCount = 0
' Loop through each row in the range
For Each row In rng.Rows
If Not row.Hidden Then
' If the row is not hidden, increment the counter
visibleRowCount = visibleRowCount + 1
End If
Next row
' Return the count of visible rows
CountVisibleRows = visibleRowCount
End Function
This VBA code prompts the user to enter a range with an input box and then calculates the number of visible (not hidden) rows within the specified range in “Sheet1” of the workbook. It handles any errors in range and displays the result or an error message in a message box.
Sub UseCountVisibleRows()
Dim ws As Worksheet
Dim rng As Range
Dim visibleRows As Long
Dim rangeAddress As String
' Set the worksheet. Modify "Sheet1" to your specific sheet name.
Set ws = ThisWorkbook.Sheets("Sheet1")
' Prompt the user to enter the range address.
rangeAddress = InputBox("Please enter the range (e.g., A1:B20):", "Range Input")
' Check if range address is not empty.
If rangeAddress <> "" Then
' Set the range based on the input. Use error handling in case of an invalid range.
On Error Resume Next
Set rng = ws.Range(rangeAddress)
If Err.Number <> 0 Then
MsgBox "Invalid range. Please try again.", vbExclamation
Exit Sub
End If
On Error GoTo 0
' Call the function to count visible rows.
visibleRows = CountVisibleRows(rng)
' Display the number of visible rows.
MsgBox "The number of visible rows in " & rangeAddress & " is: " & visibleRows, vbInformation
Else
MsgBox "No range specified.", vbExclamation
End If
End Sub