xlLastCell produces bizarre results

[jon]

I have a fairly simple Sheet with data down to E:85. When I calculate

NX = ActiveCell.SpecialCells(xlLastCell).row

the result is M:65536.

I've tried selecting and clearing all of the cells beyond the end of my real
data, but to no avail. I can share the offending Sheet with you
(individually) if you wish.

Any idea what is going on? I'm about to release an AddIn, and this problem
makes the AI blow up, which would be pretty embarrassing...



Jon

jbondy at sover dot net

 

[Nikos Yannacopoulos]

Jon,

By "selecting and clearing all of the cells beyond the end of my real data",
do you mean you used the Delete button or menu item Clear Contents? That
will not do the trick, you need to run menu item Delete, plus save the
workbook before the xlLastCell will actually return the end of your real
data.
Note: be sure to wipe out entire rows, not just the arera below the
populated columns!

HTH,
Nikos

 

[Ron de Bruin]

Read this Jon
http://www.contextures.com/xlfaqApp.html#Unused

 

[Nikos Yannacopoulos]

Jon,

By "selecting and clearing all of the cells beyond the end of my real data",
do you mean you used the Delete button or menu item Clear Contents? That
will not do the trick, you need to run menu item Delete, plus save the
workbook before the xlLastCell will actually return the end of your real
data.
Note: be sure to wipe out entire rows, not just the arera below the
populated columns!

HTH,
Nikos