Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Determining the number of Used Rows in a WorkSheet . . .

  1. #1
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Determining the number of Used Rows in a WorkSheet . . .

    Hey All,

    I am a newbie here, but am not new to VBA. I am trying to complete a large application that I wrote using Excel and VBA for work, but I am stuck on one stupid little thing.

    My problem is that I need to be able to determine the number of used rows in a WorkSheet (i.e. rows that contain at least one cell of data). Logically, I thought that the following line of code would get me this result:
    VB:
    Dim dblNumUsedRows As Double 
    dblNumUsedRows = ActiveSheet.UsedRange.Rows.Count 
    
    
    However, this code does not work for me. I have 406 rows containing data on the active sheet, but this code is returning dblNumUsedRows = 12605.

    Weird!

    Can anyone help me out? I would greatly appreciate it!

    Thanks!
    Bryan
    Last edited by Barbarr; October 21st, 2005 at 00:02. Reason: Code tags

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2004
    Location
    Canada Quebec
    Posts
    260

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Welcome to Ozgrid forum

    Did you try to save your document, and the run your code. If so I would go and your last row ie 406, highlight everything below up to row 1206 and do delete. This should solve your problem.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: Determining the number of Used Rows in a WorkSheet . . .

    FYI: Formatted Cells will count as "Used' and add to your used row total.

    Also, since .Count will be a whole number, you could Dim as Long (Not/NOT Integer, though, since Excel integers don't go up to 65,536 -- the number of possible rows.)
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  4. #4
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: Determining the number of Used Rows in a WorkSheet . . .

    I have added code tags for you in this post. Please read the rules you agreed to particularly concerning Code tags & their use.

    Please use code tags for all VBA code, including 1 liners.

    Thanks
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  5. #5
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Thanks guys!

    This is VERY informative! I wish I had found this site earlier . . . it could have saved me a lot of headaches!

    Anyhow, I will try your recommendations in my application. Basically, I have about 12 files that I am outputting individual lines of data to, and I need the new data to be apended to the end of any preexisting data within the file. If anyone has any suggestions for an improved method, I would love to hear them.

    Also, another question: is the usedrange.count method efficient? Behind the scenes, this method doesn't actually cycle through each row from start to end until it finds the last row (inefficient) does it?

    Thanks for putting my code into tags for me. I will make note to do that in the future.

    Regards,
    Bryan

    PS Nice to see some other Canadians in here!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th October 2005
    Location
    Brussels, Belgium
    Posts
    48

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Hi

    I've had problems with UsedRange before, and usually, saving the file appears to reset it. However, sometimes this is not an option.

    In such cases, and assuming that there are no blank lines in your files, you can use
    VB:
    dblNumUsedRows = ActiveSheet.columns(1).find("").row-1 
    
    
    Hope this helps...

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Thanks again guys

    I just deleting blank rows in my output files, saving them, and then rerunning my program. I used to loop through and count the number of rows, and this would have to be done every time I added to one of the files. These files are going to be approaching the Excel maximum of ~65,000, so you can see how grossly inefficient this is!

    Using the looping method and 30 "outputs", it would take my program 2 mins and 20 seconds to complete using the looping method. Now, using the count method instead of the loop, 30 "outputs" only takes about 30 seconds! What a difference!

    Now I need to find more efficiency improvements for my program, because it is going to have to be able to handle 1000 - 2000 outputs (which would currently take roughly 33 minutes, which is still much too long).

    Thanks for the suggestions!
    Bryan

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Quote Originally Posted by bdonkersgoed
    Also, another question: is the usedrange.count method efficient? Behind the scenes, this method doesn't actually cycle through each row from start to end until it finds the last row (inefficient) does it?
    No, UsedRange does not cycle. It's like have a range that's name. You can just say go to the range and it selects all the cells.

    But with either a named range or UsedRange, you don't actually need to select it--just reference it and count the rows.

    There is more than one way to find the last row and I haven't found one method yet that fits all. Depends on the layout of the worksheet. For example, in columns A to E I enter data and in column F I have a formula that does a calculation if the cell in column A is not blank. My last row for data in column A is row 50 but my formula in column F is copied down to row 1000. Then neither LastRow nor UsedRange works.

    I have to determine the last row in say column A. There are several ways to do that. If your data is contiguous (no blank cells in the referenced column), the looking for "" will work but if non-contiguous that will only find the next blank cell in the referenced column.

    So then you have to use a method that from the last row in the worksheet (65,536) and up to find the last row used. But again, if the referenced cell has formulas, the last row containing a formula will be found.
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

  9. #9
    Join Date
    20th October 2005
    Location
    Ontario, CANADA
    Posts
    69

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Quote Originally Posted by Barbarr
    No, UsedRange does not cycle. It's like have a range that's name. You can just say go to the range and it selects all the cells.

    But with either a named range or UsedRange, you don't actually need to select it--just reference it and count the rows.

    There is more than one way to find the last row and I haven't found one method yet that fits all. Depends on the layout of the worksheet. For example, in columns A to E I enter data and in column F I have a formula that does a calculation if the cell in column A is not blank. My last row for data in column A is row 50 but my formula in column F is copied down to row 1000. Then neither LastRow nor UsedRange works.

    I have to determine the last row in say column A. There are several ways to do that. If your data is contiguous (no blank cells in the referenced column), the looking for "" will work but if non-contiguous that will only find the next blank cell in the referenced column.

    So then you have to use a method that from the last row in the worksheet (65,536) and up to find the last row used. But again, if the referenced cell has formulas, the last row containing a formula will be found.
    Lucky that I don't have this problem. Initially, my output files only contain column headings and then my application just fills in about 60,000 lines underneith these headings. There is absolutely no formulae or formatting in my output files at all. These Excel files are replicating the format of a database from a third-party application, so formatting is not necessary.

    Now I have to go through my 70+ pages of code and try and find some other efficiency improvements. I started with the most obvious improvement, but the others are more subtle.

    Regards,
    Bryan

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    2nd August 2004
    Location
    Ontario - Canada
    Posts
    1,452

    Re: Determining the number of Used Rows in a WorkSheet . . .

    Someone else posted this code (sorry can't remember who) which is a variation on UsedRange. This code actually gives you the row number of the last row rather than counting the rows in the UsedRange.

    VB:
    Function FindLastRow() 
        Dim LastRow As Long 
        LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row 
        FindLastRow = LastRow 
    End Function 
    
    
    Barbara - aka The Cat Lady

    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Calculate number of rows used on Sheet/Worksheet
    By tornado in forum EXCEL HELP
    Replies: 13
    Last Post: May 31st, 2006, 00:28
  2. limit number of rows in worksheet
    By mickdonedee in forum EXCEL HELP
    Replies: 2
    Last Post: November 6th, 2005, 05:31
  3. Determining Number of Rows in Worksheet
    By tpmdtm in forum EXCEL HELP
    Replies: 1
    Last Post: February 1st, 2005, 06:03

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno