Count Rows with Data in a Macro
- 19 Comment
For this Excel tip, let’s look at an easy way to count the number of rows containing data in a spreadsheet. One way would be to do use a for loop and a counting variable, but that contains unnecessary steps to get the job done.
A simple way to count the number of rows in a spreadsheet is use the built-in Counta function in Excel. I described how to use the Counta formula in a previous post, and using it in a macro work much the same way. Let’s take a look at the syntax for the Counta Function. In able to use the result of the formula in the you must assign a variable the result returned.
rowCount = Application.WorksheetFunction.CountA(Range(“A:A”))
Looking at the syntax for the formula, you can see that this formula uses CountA as a function of the current application and worksheet. Inside the parenthesis of the CountA, place the range of cells you want to count the number of (In the example above, I told the function to count every cell with data in column A). Excel will return the number of cells with data in it and place the result in the variable rowCount.
Watch Out For
Two things you need to remember when using this function:
- The count returned includes any header row in the range of cells (you may want to subtract 1 to get the number of rows of data)
- If there are any blanks in the range you want counted, blank rows are not returned by the counta formula. Just make sure what is returned is what you want - sorting by column A first might help with the results.
One purpose I use this function for all the time is in conjunction with a for loop, using the CountA function to return the bottom number of a range to loop through. Macros can use many of the formulas built into Excel through the Application.WorksheetFunction.[function] method. We’ll look at some more of these soon.
** Excel Hints provides Excel Tips and Excel Help for All Levels of Experience.