Hi David,
I'm so happy to receive a response from you. Actually, I have a problem of a
very large file size in another workbook. I tried all the things that you and
Debra have suggested elsewhere, but I have not been able to reset the last
cell and thereby reduce the file size. In order to simulate my problem and to
remove any doubts that I may be having data or formulas in row 65536, I
opened a new workbook and filled up just 2 cells - A1 and R50 - with the
number 123. Then I hid and unhid all the rows from 51 to 65536. My last cell
now was R65536 instead of R50. Deleting all the rows from 51 to 65536 and
then saving the file, did not reset the last cell. I am not too keen on
trying the DeleteUnused macro because it appears that doesn't work well when
there are merged cells in the worksheet, and my original workbook has merged
cells all over.
To check things out more thoroughly, I entered data in 2 cells an sheet 2
as above and then hid and unhid rows 51 to 65535 only, i.e., I left row 65536
untouched. This time I had no problems resetting the last cell when I deleted
rows 51 to 65535 and saved the file.
That led me to believe that the problem was in my hiding row 65536 also. So
on sheet 3, I repeated the experiment and hid and unhid rows 35000 to 65536.
Surprisingly, the last cell got reset when I deleted these 30000 & odd rows
and saved the file.
Why is it that I am unable to reset the last cell when I hide a large number
of rows(say, beyond 35000) alongwith row 65536?
"David McRitchie" wrote:
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?