Count Rows with Data in a Macro
- 8 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.
CountA Example
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.
More Excel Tips
8 Comments on this post
Trackbacks
-
Kaustubh said:
The post is very helpful. All the hints specified are very easy to understand and are of great help. Thanks
February 19th, 2009 at 2:54 am -
john said:
Thanks Kaustubh. I’m glad your finding the site useful.
February 19th, 2009 at 9:49 am -
john said:
I can’t get this to work in Excel 2003. Is there a slightly different syntax? Or other function that will do the same thing?
September 10th, 2009 at 12:10 pm -
john said:
I don’t believe so, I’ve used it with 2003 I’m pretty sure. Can you post what you have coded?
September 10th, 2009 at 1:17 pm -
john said:
I got it working. The function didn’t seem to like calling out the columns, so I created variables to work around it. Here’s what I ended up using. I’m a bit of a novice and am learning as I go, so I’m sure there’s a more elegant solution…
Set MyRange = Sheets(“Oracle”).Range(“A:A”)
LastRow = Application.WorksheetFunction.CountA(MyRange)
Klast = “K” & LastRow
Krange = “K2:” & Klast
Range(“K2″).Select
Selection.AutoFill Destination:=Range(Krange)September 10th, 2009 at 1:56 pm -
john said:
Hmmm… I’m still not sure why that didn’t work. I did the following in 2003 and it worked fine:
Sub counta()
Sheets(“sheet1″).Select
RowCount = Application.WorksheetFunction.counta(Range(“A:A”))
MsgBox (RowCount)
End SubYou could also try something like the following which will select rows 2 through the end of the data in Column A:
Sub selectData()
RowCount = Range(“A65536″).End(xlUp).Row
Range(“A2:A” & RowCount).Select
End SubGood job finding a solution that worked for you though.
September 10th, 2009 at 3:48 pm -
claire said:
I am using counta to count cells that have text in but it is counting the blank cells as well.
I have data in 5 of the 34 cells and the rest are blank so my sum is
=counta(b2:al2) but the answer is 34.
What am I doing wrong.Thanks
ClaireFebruary 13th, 2010 at 7:34 am -
john said:
Hi Claire, check out this page (http://www.contextures.com/xlFunctions04.html)… there is a note at the bottom of the counta section that might apply to you. Hopefully that helps. If that is the case, maybe a countif formula would work better for you.
February 13th, 2010 at 10:57 am
Loading ...