Delete Empty Rows in Active Sheet

User request

Delete all empty rows in the active sheet

Option Explicit

Sub DeleteEmptyRows()
    Dim ws As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim isEmpty As Boolean
    Dim rowsToDelete As Range
    
    ' Set worksheet to active sheet
    Set ws = ActiveSheet
    
    ' Find last used row and column
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' If sheet is empty, exit
    If lastRow = 1 And lastCol = 1 And IsEmpty(ws.Cells(1, 1).Value) Then
        MsgBox "The sheet is empty.", vbExclamation
        Exit Sub
    End If
    
    ' Loop through rows from bottom to top
    For r = lastRow To 1 Step -1
        isEmpty = True
        For c = 1 To lastCol
            If Not IsEmpty(ws.Cells(r, c).Value) Then
                isEmpty = False
                Exit For
            End If
        Next c
        
        If isEmpty Then
            If rowsToDelete Is Nothing Then
                Set rowsToDelete = ws.Rows(r)
            Else
                Set rowsToDelete = Union(rowsToDelete, ws.Rows(r))
            End If
        End If
    Next r
    
    ' Delete all empty rows at once
    If Not rowsToDelete Is Nothing Then
        rowsToDelete.Delete
        MsgBox "Deleted " & rowsToDelete.Areas.Count & " empty row(s).", vbInformation
    Else
        MsgBox "No empty rows found.", vbInformation
    End If
End Sub

What the macro does

• The macro identifies the used range by finding the last row and column with data.
• It loops through each row from bottom to top to avoid issues with row deletion.
• For each row, it checks all cells in the used columns; if all are empty, the row is marked for deletion.
• Empty rows are collected in a Range object and deleted in one operation for efficiency.
• Appropriate messages are shown for empty sheet, no empty rows, or successful deletion.

How to use it

1. Open the Excel workbook and press Alt+F11 to open the VBA editor.
2. Insert a new module (Insert > Module) and paste the provided code.
3. Close the VBA editor and return to Excel.
4. Select the worksheet where you want to delete empty rows.
5. Press Alt+F8, choose 'DeleteEmptyRows', and click Run.