VBA: Fix scroll bar but keep named range

[Rowland Hamilton]

Folks:
This code fixes my scrollbars without having to save the document first, but I am getting an error: I carry over certain entries to a summary sheet, then delete the ones I don't want. On the summary sheet is a named range = the entire column AF. If I delete all the rows with populated cells in column AF, (all rows below header except total line) then my named range = #REF and it ruins my reference. How can I avoid this (populate the header row?) - Thank you - Rowland

Sub DeleteUnusedFormats(wksScroll As Worksheet)
'routine resets scrollbar by deleting unused rows below data
Dim lLastRow As Long
Dim lLastColumn As Long
Dim lRealLastRow As Long
Dim lRealLastColumn As Long

    'note: May need to place sheet.activate code before this sub is called
    wksScroll.Activate
    With Range("A1").SpecialCells(xlCellTypeLastCell)
        lLastRow = .Row
        lLastColumn = .Column
    End With

    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , _
        xlByRows, xlPrevious).Row

    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
        xlByColumns, xlPrevious).Column

    If lRealLastRow < lLastRow Then
        Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
    End If

    If lRealLastColumn < lLastColumn Then
        Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)) _
            .EntireColumn.Delete
    End If

    ActiveSheet.UsedRange   'resets LastCell
    
    'cursor default to top left cell = A1
    Application.Goto Reference:=Range("a1"), Scroll:=True
End Sub

p.s. Tried just using

 ActiveSheet.UsedRange

but not enough, eventhough the rows seem not to be populated below grand total