How to Count Rows with Data in Column Using VBA in Excel (9 Ways)

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.

Excel VBA count rows with data in 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.

Excel VBA count rows with data in column

Step-01:
➤ Go to Developer Tab >> Visual Basic Option.

Rows. Count Property

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

Rows. Count Property

After that, a Module will be created.

Rows. Count Property

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 Sub

Here, 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.

Rows. Count Property

➤ Press F5.

Then, you will get the total number of rows with data as 8 of the Sales column in a message box.

Excel VBA count rows with data in column

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

End Property

➤ Press F5.

After that, you will have 8 as the total number of rows of the Sales column in a message box.

End Property

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

Rows. Count & End Property

➤ Press F5.

Afterward, we will get 8 as the total number of rows of the Sales column in a message box.

Rows. Count & End Property

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

Selection Property

➤ Select the range of the Sales column, and then, go to the Developer Tab >> Macros Option.

Excel VBA count rows with data in column

After that, the Macro dialog box will pop up.
➤ Select the Macro name countrows4, and then, press the Run option.

Selection Property

Finally, we will get the following message box saying “Number of used rows is 8”.

Excel VBA count rows with data in column

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

FIND Function

➤ Press F5.

Finally, you will have 8 as the total number of rows of the Sales column in a message box.

FIND Function

Related Content: Excel Count Visible Rows (Formula and VBA Code)


Similar Readings:


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.

Excel VBA count rows with data in column

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 Sub

Here, 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.

non-blank rows

➤ Press F5.

Eventually, you will have 5 as the total number of non-blank rows of the Sales column in a message box.

non-blank rows

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

specific value

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

specific value

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

counting rows for specific condition

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

counting rows for specific condition

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.

Excel VBA count rows with data in 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 Sub

Here, 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.

specific text string

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

specific text string

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.

Practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo