Hi David,
Thank you very much for taking out time for me.
My hiding and unhiding the rows and columns was not for setting the print
area. I was developing a workbook of about 40 worksheets for the use of the
auditing staff in our organisation. In an attempt to tidy up the worksheets,
I hid all the rows and columns that the auditing staff did not require to
access, and was quite satisfied with the result. Then in order to continue
with the development of the workbook, I unhid the rows and columns previously
hidden. This last action shifted the last cell to row 65536 and increased the
file size to 10 MB. I now wish to reset the last cell and reduce the file
size.
So much for the why. However, I still haven't figured out the how. Deleting
the unhidden rows and then saving the file, whether done manually or through
a macro, seems to work only in the first 2 of the following 3 situations:
situation 1 - hide any number of rows, but leave the row 65536 untouched
(resetting last cell works)
situation 2 - hide less than 30000 rows along with row 65536
(resetting last cell works)
situation 3 - hide more than 35000 rows along with row 65536
(resetting last cell does not work)
I am now in the situation 3 and seem to be stuck with a large file size. As
suggested by you, I tried the DeleteUnused macro several times in a simulated
situation 3 with no success. I could not reset the last cell back to where it
should be.
I will be happy if you can suggest some other way out.
"David McRitchie" wrote:
Hi jagdish,
In Debra's macro you will see code as
myLastRow + 1
if data were found in the last row it would try to add 1 to the row number
and delete from that through the last row. The problem is the MyLastRow+1
would be referencing a row beyond the spreadsheet limits.
Now trying your data cells A1 and R50 the rest hidden, worked for me
you deleted the hidden rows and are shown all rows. Thought it looked
okay but then used Ctrl+End and Excel gavei R65535 as the lastcell.
Can't explain that for sure, but the last cell is a high water mark, if you
insert rows and then delete them you still have the high water mark. So I guess
if you delete rows that you did not have you you have essentially added rows
to the high water mark. But if you run Debra's macro once more you will see
that R50 is the last cell based on Ctrl+End
again hiding 51:65535 even though R50 was the last cell, then rechecking
the last cell is is then R65536 So it would appear that the act of hiding rows
changes the lastcell so that is beyond the hidden area.
Why are you trying to delete lots of rows that are hidden. Does this have to do
with setting a print area. If it does then deleting the unused rows should solve
you problem and you should not have to resort to setting a print area.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"jagdish.eashwar" wrote in message
...
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?