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

  • Thread starter Thread starter BEDE
  • Start date Start date
B

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?
 
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.
 
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.
 
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.
 
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.
 
Back
Top