VBA: Table Boundaries and Overflow Error 6

Question

I am trying to get the table boundaries of an excel sheet using VBA. I have already done this using C#, but now I need to try and translate that code to VBA.

One problem is that the syntax seems to be so different that the code does not look even similar, even or that it is simply doing the same thing! I think I am pretty close to figuring out the dimensions of a table but I'm getting this Run-time error '6': Overflow message.

Here's the code:

Dim currentRow As Integer
Dim totalRow As Integer
Dim column As Integer

currentRow = 2

Do
ActiveCell.Offset(1, 0).Select
totalRow = totalRow + 1
Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = False

The error occurs once it is close to being done with the Do loop. I read another forum response that was saying VB is slow, and that a potential error is that the Do loop is going too fast and thus getting an overflow. If that is the case, I am entirely unsure of how to solve it.

Does anyone have a way to fix the overflow problem?

Answer 1

Use the .End method of Range instead of looping to find the first empty cell.

Answer 2

You might like to look at UsedRange. Try

 Dim totalRow As Long

UsedRange: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.usedrange(VS.80).aspx
Data type: http://msdn.microsoft.com/en-us/library/aa164754(v=office.10).aspx