Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    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, 00:02. Reason: Code tags

  • #2
    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.

    Comment


    • #3
      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.

      Comment


      • #4
        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.

        Comment


        • #5
          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!

          Comment


          • #6
            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
            Code:
            dblNumUsedRows = ActiveSheet.columns(1).find("").row-1
            Hope this helps...

            Comment


            • #7
              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

              Comment


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

                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.

                Comment


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

                  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

                  Comment


                  • #10


                    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.

                    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.

                    Comment

                    Working...
                    X