Cells(my_crt_row,my_crt_col+1) crashes for my_crt_row>65536

[BEDE]

I tried this in Excel 2007, where the worksheets may have 1M rows.
Why does this crash?
In Excel 2000 to 2003, this thing worked fine for my_crr_row<=65536, as I
expected. But now, having more rows in a sheet, why does this not work for a
larger row number?

 

[macropod]

Hi Bede,

If you're running in 97/2003 compatibility mode (as you must if the wb is in xls format), you're still limited to 65536 rows and 256
columns per sheet.

 

[Barb Reinhardt]

I'd add something like this

Dim aWS as Excel.Worksheet
Dim myLastRow as long
Set aWS = ActiveSheet

myLastRow = aWS.Rows.Count

Replace 65536 with myLastRow and it should work.

This way you're covered if you use it in 2003 or 2007. FWIW, you probably
have the workbook opened in 2007, but it's in a compatible version.

 

[Barb Reinhardt]

Also, if you've dimensioned my_crr_row as an integer, it won't work for Excel
2007. You need to change it to Long. The upper limit of integer is less
than the # of rows in Excel 2007 native worksheets. Unfortunately, I can't
find the documentation for it.

 

[Barb Reinhardt]

Found it

Integer - integer handles the range of numbers -32,768 to 32,767.
Long - Long handles the number range -2,147,483,648 to 2,147,483,657.

I never ran into Integer/Long issues until I moved to 2007, but I imagine I
might have in 2003 under some situations.