ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   resetting last cell (https://www.excelbanter.com/excel-discussion-misc-queries/79993-resetting-last-cell.html)

jagdish.eashwar March 28th 06 12:01 PM

resetting last cell
 
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?

davesexcel March 28th 06 12:20 PM

resetting last cell
 

Here's a great site about excel ranges, with code to go to the last
cell



http://www.ozgrid.com/VBA/ExcelRanges.htm


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=527082


jagdish.eashwar March 28th 06 02:04 PM

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.

"jagdish.eashwar" wrote:

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?


David McRitchie March 28th 06 05:23 PM

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?




davesexcel March 29th 06 01:02 AM

resetting last cell
 

jagdish.eashwar Wrote:
In response to my post, I was referred to *davesexcel* site which
contains code
[/color]

Hopefully you mean David McRitchie's sight


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=527082


jagdish.eashwar March 29th 06 01:20 AM

resetting last cell
 
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?





jagdish.eashwar March 29th 06 02:48 AM

resetting last cell
 
Hi,

I was taken to www.ozgrid.com/VBA/ExcelRanges.htm when I clicked the link.
Since I received the reply form "davesexcel", I used the same words. Sorry
for the goof up.

"davesexcel" wrote:


jagdish.eashwar Wrote:
In response to my post, I was referred to *davesexcel* site which
contains code


Hopefully you mean David McRitchie's sight


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=527082

[/color]

David McRitchie March 29th 06 05:22 AM

resetting last cell
 
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?








jagdish.eashwar March 29th 06 09:01 AM

resetting last cell
 
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?








David McRitchie March 29th 06 03:15 PM

resetting last cell
 
Hi jagdish,
If you want to make a specific cell the last cell on one worksheet, try
the MakeLastCell macro on
http://www.mvps.org/dmcritchie/excel...m#MakeLastCell
but I would sugget that you unhide all rows first or you would probably be
deleting unseen data, as the purpose is to make a specific cell the
last cell. It will not expand the used area.

Don't know if you would be hit by this in some cases or not.
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected in Excel
http://support.microsoft.com/?kbid=832293
if you select more than 8,192 non-contiguous cells [areas] with your macro. Excel only
supports a maximum of 8,192 non-contiguous cells through VBA macros.

---
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"
[clipped]




All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com