resetting last cell
In response to my post, I was referred to davesexcel site which contains code
to go to the last used_cell in the worksheet. This didn't help me much
because I know how to go to the last used_cell and I know how to reset the
last cell (clear-all and delete-entire row and save). Excel is not
responding appropriately in the situation mentioned in my first post.
Regarding my observation that Excel is not resetting the last cell if the
rows hidden are more than say 35000, this is the case only if the 65536th row
is also included in the hidden rows. If I hide more than 35000 rows, say,
from 6 to 65535, I don't have any problem resetting the last cell. On the
other hand, if I include the 65536th row in the hidden rows which number
about 30000 rows (say, from row 35000 to 65536), again I don't have any
problem resetting the last cell. I can't figure out the problem.
In a blank Excel 2003 workbook, on sheet 1, I entered the number 123 in cell
A1 and in cell R50. My used range thus was A1:R50 and my last cell was R50.
I hid the rows and columns beyond this area, i.e., rows from 51 to 65536 and
columns form S to IV. When I unhid the rows and columns after using goto
A65536 and IV1, cell R65536 became the last cell. Even after deleting the
rows and columns that I had previously hidden using delete entire row/column
from the edit menu and then saving the sheet, the last cell continued to be
at R65536. The file size too had increased from 13.5 kb to 1.42 mb.
I have observed that I am unable to reset the last cell using the above
method if I have hidden a really large number of rows right upto row 65536,
say, over 35000 rows. Is there some other work around for such a situation?