What is the maximum value for row and column Range offset in VBA/Excel ?

Question

I am using microsft excel 2003. I am getting "Application-defined or object-defined error" while executing the following If statement.

If Range("MyData").CurrentRegion.Offset(i, 0).Resize(1, 1).Value = Range("MyData").CurrentRegion.Offset(i + 1, 0).Resize(1, 1).Value Then

The value of i is 58981 when I get this error.

Is there any limit on row offset ?

Thanks in Advance.

---Sam

Answer 1

excel 2003 worksheets can have a maximum of 65,536 rows (by 256 columns). i guess your region plus the big offset go over this limit.

Answer 2

Do the resize before the offset, that way you won't offset a large region so that it exceeds the worksheet size (assuming that the rest of your logic is correct):

Range("MyData").CurrentRegion.Resize(1,1).Offset(i,0)