Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Ctrl+End Behaviour (Excel 97 SR2)

    I have huge worksheets, several thousand rows each. Most recent information is in the last row, and I use Ctrl+End to get there. After deleting unnecessary information worksheets shrink to several hundreds rows. But even after saving worksheet, Ctrl+End still points to the old, now empty row well down below actual end of data. The only workaround I know is to copy everything to the new worksheet, but it is very time consuming to me. Any ideas?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    Select all unused rows beneath your data, and while selected Edit, Clear, All, and save the workbook immediately. If that does not work, repeat but select Delete instead of Claer, All.

    If you have problems with unused columns the same procedure should help.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    I use this macro as work-around, can be modified easily to become a public function:

    Sub RealLastCell()
    'Work around for the Ctrl+End 'selects rightmost corner _or!_ formatted cell' bug
    Dim nLastRow As Long, nLastCol As Integer
    With ActiveSheet
    nLastRow = .Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    nLastCol = .Cells.Find(what:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    .Cells(nLastRow, nLastCol).Select
    End With
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    Will this procedure just reset lower_rightmost corner? Is there additional code that could be added to delete all unused colums and all unused rows? For example if my data occupies the range ("A1:J499"), then I make adjustments to the data and it now occupies the range ("A1:I375"), could your code select columns J thru IV and delete, then select rows 375 thru 65536 and delete, then reset the lower_rightmost corner to ("A1:I375")?

    Just curious as I run into this alot. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    Well, it should be feasible:

    a) Is manual triggering ok ? Sometimes the events become events...

    [img]/forums/images/smilies/cool.gif[/img] What shall happen with the empty 'leftmost' columns and with the empty 'top' rows ? I know your example does not have them, but what would you expect the routine to do if you happen to have them ? I always try to make my macros as general as possible.

    c) Where do you want the selection point after the execution ?

  6. #6
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    In answer to your questions:

    a) Is manual triggering ok ? Sometimes the events become events...
    <font color=red> Manual is great, I was thinking of being able to activate from a menu.</font color=red>

    [img]/forums/images/smilies/cool.gif[/img] What shall happen with the empty 'leftmost' columns and with the empty 'top' rows ? I know your example does not have them, but what would you expect the routine to do if you happen to have them ? I always try to make my macros as general as possible. <font color=red> is it possible to ignore them? If not possible to ignore, any suggestions? </font color=red>

    c) Where do you want the selection point after the execution ? <font color=red> How about the lower_bottom_right corner? </font color=red> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    Here it is, check it before you use it for real. (Revision 2)

    Sub ClearFormatsAfterRealLastCell()
    'Work around for UsedRange includes cells with non standard formatting
    Dim nRealLastRow As Long, nUrLastRow As Long
    Dim nRealLastCol As Integer, nUrLastCol As Integer, iSave As Integer
    Dim sDlgTitle As String

    sDlgTitle = "Macro ClearFormatsAfterRealLastCell"
    With ActiveSheet
    'Check whether worksheet is protected
    If .ProtectContents = True Then
    MsgBox "Please unprotect sheet '" & .Name & "' first, then re-run this macro."
    Exit Sub
    End If
    'Determine 'RealLastCell' and the 'CtrlEnd' cell
    With .Cells
    nRealLastRow = .Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    nRealLastCol = .Find(what:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    With .SpecialCells(xlCellTypeLastCell)
    nUrLastRow = .Row
    nUrLastCol = .Column
    End With
    End With
    'Jump to RealLastCell
    .Cells(nRealLastRow, nRealLastCol).Select
    If nUrLastRow > nRealLastRow Or nUrLastCol > nRealLastCol Then
    iSave = MsgBox("Operation can not be undone. Save workbook now ?", vbYesNoCancel, sDlgTitle)
    If iSave = vbCancel Then Exit Sub
    If iSave = vbYes Then .Parent.Save
    'Clear surplus formatting where required, safer than deleting cells, its still Microsoft
    If nUrLastRow > nRealLastRow Then .Rows(nRealLastRow + 1 & ":" & nUrLastRow).ClearFormats
    If nUrLastCol > nRealLastCol Then .Rows(nRealLastCol + 1 & ":" & nUrLastCol).ClearFormats
    'Save it in order CtrlEndCell becomes the RealLastCell
    iSave = MsgBox("'Surplus' cell formats were cleared. Save workbook now ?", vbYesNo, sDlgTitle)
    If iSave = vbYes Then .Parent.Save
    Else
    MsgBox "There are no formats after the selected 'Ctrl+End' cell", vbInformation, sDlgTitle
    End If
    End With
    End Sub

    You will have to indent it yourself for better readability, this site suppresses leading blanks

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Ctrl+End Behaviour (Excel 97 SR2)

    The site does compress leading blanks- if you post it as straight text. Just like any straight HTML.

    But tere is a way to display it as code. See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=ann&Number=30349&page=0 &view=expanded&sb=5&o=0&part=>this post</A> for an explanation on how to format code for the forum.

    Here's your code again, formatted. With a few line splits to make it look nice on the forum.
    <pre>Sub ClearFormatsAfterRealLastCell()
    'Work around for UsedRange includes cells with non standard formatting
    Dim nRealLastRow As Long, nUrLastRow As Long
    Dim nRealLastCol As Integer, nUrLastCol As Integer, iSave As Integer
    Dim sDlgTitle As String

    sDlgTitle = "Macro ClearFormatsAfterRealLastCell"
    With ActiveSheet
    'Check whether worksheet is protected
    If .ProtectContents = True Then
    MsgBox "Please unprotect sheet '" & .Name & _
    "' first, then re-run this macro."
    Exit Sub
    End If
    'Determine 'RealLastCell' and the 'CtrlEnd' cell
    With .Cells
    nRealLastRow = .Find(what:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    nRealLastCol = .Find(what:="*", SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    With .SpecialCells(xlCellTypeLastCell)
    nUrLastRow = .Row
    nUrLastCol = .Column
    End With
    End With
    'Jump to RealLastCell
    .Cells(nRealLastRow, nRealLastCol).Select
    If nUrLastRow > nRealLastRow Or nUrLastCol > nRealLastCol Then
    iSave = MsgBox("Operation can not be undone. Save workbook now ?", _
    vbYesNoCancel, sDlgTitle)
    If iSave = vbCancel Then Exit Sub
    If iSave = vbYes Then .Parent.Save
    'Clear surplus formatting where required,
    'safer than deleting cells, its still Microsoft
    If nUrLastRow > nRealLastRow Then
    .Rows(nRealLastRow + 1 & ":" & nUrLastRow).ClearFormats
    End If
    If nUrLastCol > nRealLastCol Then
    .Rows(nRealLastCol + 1 & ":" & nUrLastCol).ClearFormats
    End If
    'Save it in order CtrlEndCell becomes the RealLastCell
    iSave = MsgBox("'Surplus' cell formats were cleared. Save workbook now ?", _
    vbYesNo, sDlgTitle)
    If iSave = vbYes Then .Parent.Save
    Else
    MsgBox "There are no formats after the selected 'Ctrl+End' cell", _
    vbInformation, sDlgTitle
    End If
    End With
    End Sub
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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