How to count rows of data in a sheet?

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

Answer 1

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.

Answer 2

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

Answer 3

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!

Answer 4

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!