Excel short scroll bar a million empty rows

NoSparks · #1 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

LaPlume · #2 10-11-2023, 07:03 AM

<If you're open to a VBA suggestion, this code that MARK858 over posted a few years ago has worked for me in the past.>

Thanks! I'll brush up on VBA and give the code a try.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel rows hidden when it is empty	allex011	Excel Programming	5	03-16-2020 09:55 PM
Delete all empty rows in all tables	fbucaram	Word VBA	6	01-05-2018 03:04 PM
Delete Empty Table Rows	cltay87	Word VBA	4	02-27-2017 04:23 AM
Macro to delete all empty rows from all tables	braddgood	Word VBA	15	10-02-2015 01:54 PM
Delete All empty Rows - Print - Undo all Rows deleted	Bathroth	Word VBA	1	10-01-2014 01:40 PM