Tanjima Hossain
If you are looking for some of the easiest ways to count rows with data in a column using Excel VBA, then you are in the right place. So, let’s start with our main article to know more about counting rows with data in a column easily.
Download Workbook
9 Ways to Count Rows with Data in Column Using Excel VBA
Here, we have the following dataset containing sales records of a company. We will use different columns with data from this dataset to explain different ways to count rows based on the data of a column.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Method-1: Using VBA Rows.Count Property to Count Rows with Data in Column in Excel
Here, we will be counting the rows of the Sales column with sales values for the products using the Rows. Count property of VBA.
Step-01:
➤ Go to Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.
After that, a Module will be created.
Step-02:
➤ Write the following code
Sub countrows1()
Dim X As Integer
X = Range("D4:D11").Rows.Count
MsgBox "Number of used rows is " & X
End SubHere, we have declared X as Integer, “D4:D11” is the range on the basis of which column we are counting rows and finally we have assigned the row number to X.
A message box (MsgBox) will give us the result as the total number of rows.
➤ Press F5.
Then, you will get the total number of rows with data as 8 of the Sales column in a message box.
Read More: Excel VBA to Count Rows with Data (4 Examples)
Method-2: Using End Property to Count Rows with Data in Column
In this section, we will use the End property of VBA to count the rows with sales values of the Sales column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows2()
Dim X As Integer
X = Range("D4").End(xlDown).Row
MsgBox "Number of used rows is " & (X - 3)
End SubHere, we have declared X as Integer, “D4” is the start value of the range on the basis of which column we are counting rows and finally we have assigned the row number to X. X will give us the last used row number not the total used rows of this column. So, to get the total row number we have subtracted 3 (Starting Row Number-1 = 4-1 = 3) from X.
A message box (MsgBox) will appear with the total number of rows.
➤ Press F5.
After that, you will have 8 as the total number of rows of the Sales column in a message box.
Read More: How to Count Rows with Data in Excel (4 Formulas)
Method-3: Using the Combination of Rows.Count Property and End Property
Here, we will use the combination of the two properties of VBA like the Rows. Count property and the End property together to count the total rows of the Sales column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows3()
Dim X As Integer
X = Cells(Rows.Count, 4).End(xlUp).Row
MsgBox "Number of used rows is " & (X - 3)
End SubHere, we have declared X as Integer, 4 in (Rows. Count, 4) is for the Sales column on the basis of which column we are counting rows and finally we have assigned the row number to X.
X will return the last used row number not the total used rows of this column. So, to get the total row number we have subtracted 3 (Starting Row Number-1 = 4-1 = 3) from X.
A message box (MsgBox) will appear with the total number of rows.
➤ Press F5.
Afterward, we will get 8 as the total number of rows of the Sales column in a message box.
Related Content: How to Count Rows with Multiple Criteria in Excel (6 Methods)
Method-4: Using VBA Selection Property to Count Rows with Data in Column in Excel
You can use the Selection property also to count the total rows based on data of the Sales column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows4()
Dim X As Integer
X = Selection.Rows.Count
MsgBox "Number of used rows is " & X
End SubHere, we have declared X as Integer, the rows of the selected range will be counted here, and finally, we have assigned the row number to X.
A message box (MsgBox) will give us the result as the total number of rows.
Then, we have to return to the main sheet.
➤ Select the range of the Sales column, and then, go to the Developer Tab >> Macros Option.
After that, the Macro dialog box will pop up.
➤ Select the Macro name countrows4, and then, press the Run option.
Finally, we will get the following message box saying “Number of used rows is 8”.
Read More: How to Count Rows with VBA in Excel (5 Approaches)
Method-5: Count Rows with Data in Column Using FIND Function
Here, we will use the FIND function to count the rows with text strings in the Region column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub CountRows5()
Dim X As Integer
Dim rng As Range
Set rng = Range("C4:C11")
With rng
X = .Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
End With
MsgBox "Number of used rows is " & (X - 3)
End SubHere, we have declared X as Integer, rng as Range, “C4:C11” is the range on the basis of which column we are counting rows and finally we have assigned it to rng.
We have used the WITH statement to avoid the repetition of using the object name like rng.
Using the FIND function, X will give us the last used row number not the total used rows of this column. So, to get the total row number we have subtracted 3 (Starting Row Number-1 = 4-1 = 3) from X.
A message box (MsgBox) will appear with the total number of rows.
➤ Press F5.
Finally, you will have 8 as the total number of rows of the Sales column in a message box.
Related Content: Excel Count Visible Rows (Formula and VBA Code)
Similar Readings:
- Count Rows in Group with Pivot Table in Excel (Step-by-Step Guideline)
- Excel VBA: Count Rows in a Sheet (5 Examples)
Method-6: Count Non-Blank Rows with Data in Column Using VBA
Here, we have some blank cells in the Sales column (we have removed some values for this method) and using a VBA code we will only count the total number of rows with values.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows6()
Dim X As Long
Dim Y, rng As Range
Set rng = Range("D4:D11")
With rng
For Each Y In .Rows
If Application.CountA(Y) > 0 Then
X = X + 1
End If
Next
End With
MsgBox "Number of used rows is " & X
End SubHere, we have declared X as Long, Y, and rng as Range, “D4:D11” is the range on the basis of which column we are counting rows and finally we have assigned it to rng.
The FOR loop will check if each of the cells of this range contains any value using the COUNTA function and for the cells having values, X will be incremented by 1 each time.
Finally, we will get the total row numbers with non-blank cells through a message box.
➤ Press F5.
Eventually, you will have 5 as the total number of non-blank rows of the Sales column in a message box.
Read More: How to Count Filtered Rows in Excel with VBA (Step-by-Step Guideline)
Method-7: Count Rows with a Specific Value
Here, we will count the total number of rows with a sales value of $2,522.00 from the Sales column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows7()
Dim X As Long
Dim Y, rng As Range
Set rng = Range("D4:D11")
With rng
For Each Y In .Rows
If Application.CountIf(Y, 2522) > 0 Then
X = X + 1
End If
Next
End With
MsgBox "Number of used rows is " & X
End SubHere, we have declared X as Long, Y, and rng as Range, “D4:D11” is the range on the basis of which column we are counting rows and finally we have assigned it to rng.
The FOR loop will check if any of the cells of this range contain the sales value 2522 using the COUNTIF function and for the cells having this value, X will be incremented by 1 each time.
Finally, we will get the total row numbers with the value of 2522 through a message box.
➤ Press F5.
Ultimately, you will have 3 as the total number rows of the Sales column containing the sales value of $2,522.00 in a message box.
Read More: Excel VBA: Count Rows with Specific Data (8 Examples)
Method-8: Count Rows with Values Greater Than a Specific Value
In this section, we will count the total number of rows with values greater than $3000.00 in the Sales column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows8()
Dim X As Long
Dim Y, rng As Range
Set rng = Range("D4:D11")
With rng
For Each Y In .Rows
If Application.CountIf(Y, ">3000") > 0 Then
X = X + 1
End If
Next
End With
MsgBox "Number of used rows is " & X
End SubHere, we have declared X as Long, Y, and rng as Range, “D4:D11” is the range on the basis of which column we are counting rows and finally we have assigned it to rng.
The FOR loop will check if any of the cells of this range contain the sales value greater than 3000 using the COUNTIF function (or you can try for any other conditions like less than, greater than, or equal to, etc.) and for the cells having this value, X will be incremented by 1 each time.
Finally, we will get the total row numbers with values greater than 3000 through a message box.
➤ Press F5.
Afterward, you will have 3 as the total number rows of the Sales column containing the sales values greater than $3,000.00 in a message box.
Read More: How Excel Count Rows with Value (8 Ways)
Method-9: Count Rows with a Specific Text String
We will get the number of rows here containing apple exactly or partially in the Product column.
Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code
Sub countrows9()
Dim X As Long
Dim Y, rng As Range
Set rng = Range("B4:B11")
With rng
For Each Y In .Rows
If Application.CountIf(Y, "*apple*") > 0 Then
X = X + 1
End If
Next
End With
MsgBox "Number of used rows is " & X
End SubHere, we have declared X as Long, Y, and rng as Range, “B4:B11” is the range on the basis of which column we are counting rows and finally we have assigned it to rng.
The FOR loop will check if any of the cells of this range contain the text string “apply” using the COUNTIF function (here, it will count for both of the exact matches and partial matches by using the Asterisk symbol before and after the string), and for the cells having this value, X will be incremented by 1 each time.
Finally, we will get the total row numbers with text string apple through a message box.
➤ Press F5.
Then, you will have 2 as the total number rows of the Product column containing the text strings Apple and Pineapple in a message box.
Read More: How to Count Rows with Text in Excel (Easiest 8 Ways)
Practice Section
For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.
Conclusion
In this article, we tried to cover the ways to count rows with data in a column using Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
