Reset last cell

[azbycx]

I have unwittingly applied a formula to an entire column that changed my last cell to KN1048576. That is all the rows but not all the columns (I have data up to KN). This is making the spreadsheet almost unusable because of the enormous range.

All advice I have read says to delete unused rows and columns, but I am told I don't have enough system resources to do this. I do not wish to start over, and I have made many subsequent changes since my last backup.

Is there any advice on how to fix this?

As an aside -- Microsoft says: "When you format whole rows and columns, some types of formatting can cause the last cell to be set to a cell far below or to the right of the actual range that is in use." What types of formatting could do this? I've been formatting whole rows and columns for a while now, and this has never happened.

Thanks!

 

[Boller]

Here's what I use to re-set the last cell, but it relies upon being able to

delete all unwanted rows and columns :-

Sub ResetLastCell()
Dim LastCell As Range
Dim lcAddress$, rw&, col%, alphaCol$
Dim Msg$, M%
Dim R As Variant, c As Variant, chk%
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
Set LastCell = _
    Cells(Range([A1], ActiveSheet.UsedRange).Rows.Count, _
    Range([A1], ActiveSheet.UsedRange).Columns.Count)
lcAddress = LastCell.Address(False, False)
rw = LastCell.Row
col = LastCell.Column
alphaCol = Left(lcAddress, (col < 27) + 2)
If lcAddress = "A1" Then GoTo x
If Application.CountA(Columns(col)) = 0 Or _
    Application.CountA(Rows(rw)) = 0 Then
    Msg = "The last cell in " & ActiveSheet.Name & _
        " is cell " & lcAddress & _
        "." & vbCrLf & vbCrLf & "Row " & rw & _
        " , Column " & col & " ( " & alphaCol & " )" & vbCrLf & vbCrLf & _
        "Do you wish to reset this to another cell?"
    M = MsgBox(Msg, vbYesNoCancel, "Reset Last Cell")
Else
x:
    MsgBox "The last cell in " & ActiveSheet.Name & _
    " has been reset to cell " & lcAddress & _
    "." & vbCrLf & vbCrLf & "Row " & rw & _
    " , Column " & col & " ( " & alphaCol & " )"
    Exit Sub
End If
If M = vbCancel Or M = vbNo Then Exit Sub
If M = vbYes Then
    On Error Resume Next
    Application.DisplayAlerts = False
    Set R = Application.InputBox("Select the required last row", Type:=8)
    Set c = Application.InputBox("Select the required last column", Type:=8)
    Application.DisplayAlerts = True
    If R Is Nothing And c Is Nothing Then GoTo e
    If Not R Is Nothing And c Is Nothing Then GoTo delR
    If R Is Nothing And Not c Is Nothing Then GoTo delC
    If Not R Is Nothing And Not c Is Nothing Then GoTo delRC
End If
delR:
    On Error GoTo 0
    chk = MsgBox("All rows after row " & R.Row & " will be permanently deleted." _
    & vbCrLf & vbCrLf & "Are you sure you want to continue?", vbYesNoCancel, "ALERT !")
    If chk = vbCancel Or chk = vbNo Then Exit Sub
    Rows(R.Row + 1 & ":" & Rows.Count).Delete
    Call SetLastCell
    GoTo e
delC:
    On Error GoTo 0
    chk = MsgBox("All columns after column " & c.Column & " will be permanently deleted." _
    & vbCrLf & vbCrLf & "Are you sure you want to continue?", vbYesNoCancel, "ALERT !")
    If chk = vbCancel Or chk = vbNo Then Exit Sub
    Range(Columns(c.Column + 1), Columns(Columns.Count)).Delete
    Call SetLastCell
    GoTo e
delRC:
    On Error GoTo 0
    chk = MsgBox("All rows after row " & R.Row & " and all columns after column " & c.Column & _
    " will be permanently deleted." & vbCrLf & vbCrLf & _
    "Are you sure you want to continue?", vbYesNoCancel, "ALERT !")
    If chk = vbCancel Or chk = vbNo Then Exit Sub
    Rows(R.Row + 1 & ":" & Rows.Count).Delete
    Range(Columns(c.Column + 1), Columns(Columns.Count)).Delete
    Call SetLastCell
    GoTo e
e:
Set R = Nothing
Set c = Nothing
On Error GoTo 0
End Sub

If this doesn't work :

- Have you been able to delete the formula from the unwanted rows in the formula column?


- Are you able to delete 1 row (or a few rows) starting from the bottom?