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

## 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 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. ➤ Press F5.

Then, you will get the total number of rows with data as 8 of the Sales column in a message box. ### 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:
➤ 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. ➤ 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:
➤ 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. ➤ 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:
➤ 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. ➤ 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:
➤ 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. ➤ 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)

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

## Related Articles #### 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  5 Excel Hacks You Never Knew  