Excel has so many powerful options that can be utilized, and we have described a lot of them so far.

If we want to find out the number of rows in our table, there are many ways to do so. In the text below, we are going to show some of the easiest ones.

Tutorial Content

## Count Rows with Formula

For the example, we will use a simple list of different fruits.

Now, there is no need to say that, for this example, it is pretty easy to count the rows, as their numbers are shown on the left side.

This example is simplified but the formula can be used in a table of any kind.

We will input a formula in **cell A17**. The formula is as follows:

1 |
=COUNTA(A2:A16) |

Keep in mind that we did not want to include the first row from the count.

**COUNTA **formula has the following syntax:

1 |
=COUNTA (value1, [value2], ...) |

It has two arguments:

- value1 – An item, cell reference, or range.
- value2 – [optional] An item, cell reference, or range.

This formula returns the count of cells in our range that contain either text, number, logical value, error value, or any value that is not empty.

Our value is our range, and we got the result as follows:

If we had empty cells in our range, we would use a different formula- **COUNTBLANK**. This formula counts only empty cells.

So, if we had empty cells in our range, we could use this formula.

We will delete a couple of entries in our table, keep our existing formula, but will add **COUNTBLANK** beneath.

Finally, we will add these two numbers together, and get the number of rows in our table:

## Count Rows with VBA

To count the rows with VBA code, you have to open the **VBA editor** either by going to Developers tab >> Code >> Macros or simply by clicking **ALT+F11**, and entering the following code:

1 2 3 4 5 6 7 |
Sub CountRows() Dim final_row As Long final_row = Cells(Rows.Count, 1).End(xlUp).Row MsgBox (final_row) End Sub |

For this code, we first declared a variable **final_row** to be the **Long** type. This type is used when working with a large set of numbers.

**Rows.Count** gives us the number of rows in a worksheet. Excel checks each value, from our final row by moving up.

For the final part, we wanted that our code to print out the message of how many rows are in our table, so we get this message after we run our code:

If we want to exclude the first row, we can add the following part in our code, right after the line of code in which we declared our variable:

1 |
final_row = final_row – 1 |

Remember that this code takes even empty cells into the consideration.

There is a problem that we could if using the code above for different tables. We could have some columns that contain more rows than others, like in the picture below:

If we execute our code now, the result will be number 16 again, which is not the correct number as we have 18 rows in **column C**. We have to change our code a little bit:

1 2 3 4 5 |
Sub CountRows2() Dim final_row As Long final_row = Cells.Find(What:="*", SearchDirection:=xlPrevious).Row MsgBox (final_row) End Sub |

The second line in the code is now looking for any data in our table. The **xlPrevious** indicates Excel to search for a value in the last Excel sell and then move from right to left from the last row to the first row until it finds a non-blank cell.

When we execute it, we will have the correct result, which is 18:

The second line in the code is now looking for any data in our table. The **xlPrevious** indicates Excel to search for a value in the last Excel sell and then move from right to left from the last row to the first row until it finds a non-blank cell.

When we execute it, we will have the correct result, which is 18: