Ctrl+End Behaviour (Excel 97 SR2)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Well, it should be feasible:

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

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 ?

Reply | Quote

In answer to your questions:

a) Is manual triggering ok ? Sometimes the events become events…
Manual is great, I was thinking of being able to activate from a menu.

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. is it possible to ignore them? If not possible to ignore, any suggestions?

c) Where do you want the selection point after the execution ? How about the lower_bottom_right corner?

Reply | Quote

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 this post 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.

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

Reply | Quote