Microsoft Office Forums - View Single Post - Excel short scroll bar a million empty rows

NoSparks · 10-11-2023, 04:41 AM

If you're open to a VBA suggestion, this code that MARK858 over at MrE posted a few years ago has worked for me in the past.
Don't know if it will for you but might be worth giving it a try.

Code:

Sub LoseThatWeightx()

    Dim xx As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

    With ActiveSheet
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                              LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
        .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
    End With
    
    xx = Application.ActiveSheet.UsedRange.Rows.Count
    Application.ScreenUpdating = True
End Sub