locked
How to count rows of data in a sheet? RRS feed

  • Question

  • I’ve tried a few different ways to count rows in a sheet and I keep getting an error message: run-time error 6, overflow

     

    Workbooks.Open ("C:\Book2.xls") 'open the workbook you are copying from

    Windows("Book20.xls").Activate

    intRowsToCheck = ActiveWorkbook.Sheets("Sheet2").UsedRange.Rows.Count

     

    The third line of code causes the error.  All I’m trying to do is count the used rows in the sheet; nothing is working.

     

    Any ideas about what I’m doing wrong?

     

    Thanks!

    Ryan---

    Wednesday, June 9, 2010 8:33 PM

Answers

  • Try using:
    Dim intRowsToCheck as Long

    If you look in VBA's help, you'll see that there's an upper limit on Integers
    and you're exceeding it.

    There's an upper limit on Long's, too, but you'll be ok in this case!

    ryguy72 wrote:


    I've tried a few different ways to count rows in a sheet and I keep getting an error message: run-time error 6, overflow



    Workbooks.Open ("C:\Book2.xls") 'open the workbook you are copying from

    Windows("Book20.xls").Activate

    intRowsToCheck = ActiveWorkbook.Sheets("Sheet2").UsedRange.Rows.Count



    The third line of code causes the error. All I'm trying to do is count the used rows in the sheet; nothing is working.



    Any ideas about what I'm doing wrong?



    Thanks!

    Ryan---

    --
    Dave Peterson

    • Marked as answer by ryguy72 Thursday, June 10, 2010 1:36 PM
    Wednesday, June 9, 2010 9:38 PM

All replies

  • When I download and run the code here, it works perfectly fine:

    http://www.xtremevbtalk.com/showthread.php?t=289117

    For some really odd reason, this line of code does not work for me when I use my own two files:

    intRowsToCheck = Workbooks("From.xls").Sheets("temp").Cells.Find("*", Range("A1"), , , xlByRows, xlPrevious).Row

    If I comment out that one line and use this:

    For A = 2 To 100

     

    The code runs perfectly fine.  However, hard-coding the intRowsToCheck totally defeats a major purpose of the overall code.

    Wednesday, June 9, 2010 9:29 PM
  • Try using:
    Dim intRowsToCheck as Long

    If you look in VBA's help, you'll see that there's an upper limit on Integers
    and you're exceeding it.

    There's an upper limit on Long's, too, but you'll be ok in this case!

    ryguy72 wrote:


    I've tried a few different ways to count rows in a sheet and I keep getting an error message: run-time error 6, overflow



    Workbooks.Open ("C:\Book2.xls") 'open the workbook you are copying from

    Windows("Book20.xls").Activate

    intRowsToCheck = ActiveWorkbook.Sheets("Sheet2").UsedRange.Rows.Count



    The third line of code causes the error. All I'm trying to do is count the used rows in the sheet; nothing is working.



    Any ideas about what I'm doing wrong?



    Thanks!

    Ryan---

    --
    Dave Peterson

    • Marked as answer by ryguy72 Thursday, June 10, 2010 1:36 PM
    Wednesday, June 9, 2010 9:38 PM
  • Thanks Dave!  You are totally right.  When I saw your response I immediately knew what the mistake was.  Initially I thought it was something else.  Excel's error messages are not the most helpful.  Maybe if it said something like, '32,767 limit exceeded...' I would have figured it out.

    Thanks again!

    Thursday, June 10, 2010 1:36 PM
  • Sub letUsCountTheRows()

    Dim CountOfRows as integer

    CountOfRows =Range("A1").CurrentRegion.Rows.Count

    MsgBox CountOfRows

    End Sub

    This code is a life saver for me, MsgBox returns the number of rows starting from A1 , u can dynamically change starting row such as

    CountofRows= Range(Inputbox("enter the starting row adress  for instance - A5 or C9 - ")).Currentregion.rows.count

    when the line above executed by excel InputBox will ask you the starting row and will give you the answer, try it!

    Thursday, June 10, 2010 2:46 PM