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.

**Table of Contents**hide

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

**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 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**:

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

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

➤ 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 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)**

**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 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**:

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

➤ 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 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**:

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

➤ Press **F5**.

Then, you will have **2 **as the total number rows of the **Product **column containing the text strings ** Apple **and

**in a message box.**

*Pineapple***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.