+ Reply to Thread
Results 1 to 12 of 12

resetting last cell

  1. #1
    jagdish.eashwar
    Guest

    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?

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,872
    Here's a great site about excel ranges, with code to go to the last cell



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

  3. #3
    jagdish.eashwar
    Guest

    RE: 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?


  4. #4
    David McRitchie
    Guest

    Re: 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"
    <jagdisheashwar@discussions.microsoft.com> 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?




  5. #5
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,872
    Quote Originally Posted by jagdish.eashwar
    In response to my post, I was referred to davesexcel site which contains code
    [/color]
    Hopefully you mean David McRitchie's sight

  6. #6
    jagdish.eashwar
    Guest

    Re: 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"
    > <jagdisheashwar@discussions.microsoft.com> 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?

    >
    >
    >


  7. #7
    jagdish.eashwar
    Guest

    Re: 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]

  8. #8
    David McRitchie
    Guest

    Re: 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" <jagdisheashwar@discussions.microsoft.com> wrote in message
    news:840C730A-B3D3-4678-8605-11035D29C6EA@microsoft.com...
    > 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"
    > > <jagdisheashwar@discussions.microsoft.com> 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?

    > >
    > >
    > >





  9. #9
    jagdish.eashwar
    Guest

    Re: 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" <jagdisheashwar@discussions.microsoft.com> wrote in message
    > news:840C730A-B3D3-4678-8605-11035D29C6EA@microsoft.com...
    > > 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"
    > > > <jagdisheashwar@discussions.microsoft.com> 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?
    > > >
    > > >
    > > >

    >
    >
    >
    >


  10. #10
    David McRitchie
    Guest

    Re: 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" <jagdisheashwar@discussions.microsoft.com>
    [clipped]



  11. #11
    jagdish.eashwar
    Guest

    Re: resetting last cell

    Hi David,

    I tried the MakeLastCell macro, but that didn't work either. I think I'll
    take a break and then take up the task of recreating my workbook. Thank you
    very much for your time. If I stumble upon an easy solution, I'll get back to
    you.

    "David McRitchie" wrote:

    > 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" <jagdisheashwar@discussions.microsoft.com>
    > [clipped]
    >
    >
    >


  12. #12
    David McRitchie
    Guest

    Re: resetting last cell

    Did it apologize for failing.
    Guess I've forgotten this a bit what was and what no longer is
    -- use of last rows, protected cells, hidden rows/columns, merged cells

    "jagdish.eashwar" <jagdisheashwar@discussions.microsoft.com> wrote
    > I tried the MakeLastCell macro, but that didn't work either. I think I'll
    > take a break and then take up the task of recreating my workbook. Thank you
    > very much for your time. If I stumble upon an easy solution, I'll get back to
    > you.
    >
    > "David McRitchie" wrote:
    > > 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 suggest 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.




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1