View Single Post
  #4   Report Post  
Old March 28th 06, 05:23 PM posted to microsoft.public.excel.misc
David McRitchie
 
Posts: n/a
Default resetting last cell

Hi jagdish,
If you have data in the last row, currently 65536, then that is the
last row of data whether hidden or not. It's ambiguous what you want
or get other than you saying you get what you want.

Any subroutine that specifically uses 65536 for the count of rows ; or uses 255 or "IV"
for the count of columns or does not use Long for the variables referring to row or column
should be immediately suspect, as the number of rows and columns is not fixed
in stone and never was, has changed in the past and both will be increased in Excel 12.
I guess coding in that manner is called "job security" with builtin obsolescence.

Any cell with a space, and any cell with a formula has content is not empty by definition.
Test with a worksheet formula =ISBLANK(K45)
So the problem is actually of your own creation trying to place hidden information/data
in your last rows/columns. You will have to fixeliminate that method first, it should NOT be used.

I would suggest fixing all of the worksheets at once, and when the macro finishes,
don't forget to save the workbook, or nothing will have been accomplished. See
Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish
http://www.contextures.com/xlfaqApp.html#Unused
use the macro DeleteUnused don't even bother with trying to do this manually.
---HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page: http://www.mvps.org/dmcritchie/excel/search.htm "jagdish.eashwar"
wrote

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.

[omitted] 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?