Oct 16 2008

Count Rows with Data in a Macro

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.


[ad#in-post-ad]

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

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          
TAGS: ,

19 Comments on this post

Trackbacks

  1. 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
  2. john said:

    Thanks Kaustubh. I’m glad your finding the site useful.

    February 19th, 2009 at 9:49 am
  3. 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
  4. 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
  5. 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
  6. 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 Sub

    You 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 Sub

    Good job finding a solution that worked for you though.

    September 10th, 2009 at 3:48 pm
  7. 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
    Claire

    February 13th, 2010 at 7:34 am
  8. 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
  9. ribs said:

    I’m trying to import spreadsheets that contain a number of fixed heading rows, the number of fixed headings varies from spreadsheet to spreadsheet. How do I determine how many fixed heading rows there are on a spreadsheet? I’ve tried using myRange.CurrentRegion.ListHeaderRows but that always returns 0

    April 29th, 2010 at 12:05 pm
  10. john said:

    Hi Ribs – you could try =COUNTA(A1:C1). Let me know if that’s not what you’re looking for.

    April 29th, 2010 at 1:27 pm
  11. ribs said:

    Thanks John, it turns out what I needed to work with were the Pane, FreezePane, Split, and SplitRows properties

    April 30th, 2010 at 1:37 pm
  12. Steve S said:

    John – I found this page via a Google search and your “RowCount” code was EXACTLY what I was looking for and worked perfectly.

    Question: I now want to use ‘RowCount’ to call out the length of an auto-fill function. For example, when I recorded the macro, I only autofilled from D34 to D41, but now I want to modify the macro to be D34 to D’RowCount’ so that no matter how many rows my different files have, it will always fill down to the last row.

    Can you tell me how to do this?

    Thanks!

    April 15th, 2011 at 6:41 pm
  13. Steve S said:

    Was able to answer my own question, using concatenate feature…

    Range(“D34:D” & RowCount)

    April 18th, 2011 at 11:05 am
  14. Philip Pointer said:

    Works perfectly. Thanks so much!

    July 12th, 2011 at 11:10 am
  15. michelle said:

    how can i use this in date?
    i want to know the numbers of days that critical items.
    im creating a inventory system..
    thanks.

    April 13th, 2012 at 12:40 am
  16. SolarBrian said:

    I didn’t have much success with the above methods in excel 2007, but something like this worked for me:

    Set MyRange = Sheets(“Sheet1″).Range(“A:A”)
    LastRow = MyRange.Count
    Cells(1, 1).Values = LastRow

    October 30th, 2012 at 7:53 am
  17. Mark said:

    Tried the original statement in Excel 2003 and did not work for me when i copied and pasted into Macro, but found out it was because the “” used in the formula were not being treated as quotes. Just needed to replace the original quotes around the A:A and I was good to go.

    Thanks for the post.

    December 11th, 2012 at 10:35 am
  18. Vidhyadhar said:

    How to split a data file into five rows each; every club of 5 rows I wish to keep a blank row, please help.
    Vidhya

    February 25th, 2013 at 10:40 am
  19. Vidhyadhar said:

    How to split a data file into five rows each using Macro; every club of 5 rows I wish to keep a blank row, please help.
    Vidhya

    February 25th, 2013 at 10:42 am

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories