How to Remove Multiple Blank Rows in Excel Using Formula (With Examples & VBA)

How to Remove Multiple Blank Rows in Excel Using Formula (With Examples & VBA)

by

Removing blank rows in Excel is a common task, especially with big datasets. Excel has built-in tools like Go To Special. But, using formulas and VBA can make it easier and faster. This guide will show you how to remove multiple blank rows using formulas and give you VBA code for different situations.

📌 Why Remove Blank Rows in Excel?

Blank rows can:

  • Disrupt sorting and filtering.
  • Cause errors in formulas and PivotTables.
  • Make data harder to read and analyze.

Now, let’s look at ways to clean up your spreadsheet.

✅ Method 1: Use Formula to Filter Out Blank Rows (No VBA)

Example Dataset:

Name Age City
John 25 New York
     
Alice 30 Chicago
     
Bob 28 Boston

We want to remove the empty rows (2 and 4).

Step-by-step Instructions:

  1. Add a helper column to check if the row is blank.
  2. Use a formula to identify non-blank rows.
  3. Use FILTER or SORT functions to return only the non-blank rows.

Step 1: Helper Column Formula

In column D (or any free column), enter:

=IF(COUNTA(A2:C2)=0, "", ROW())

This formula checks if the row is blank. If all cells are blank, it returns an empty string.

Step 2: Extract Non-Blank Rows Using

FILTER (Excel 365/Excel 2019+)

=FILTER(A2:C6, D2:D6<>"")

This formula will return only the rows where the helper column is not empty.

🔁 Method 2: Use Excel VBA to Delete Blank Rows Automatically

VBA is great for removing blank rows quickly, especially with big spreadsheets or repetitive tasks.

✅ VBA Example 1: Delete Entire Blank Rows in a Range

Sub DeleteBlankRowsInRange()
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("A2:C100")

    For i = rng.Rows.Count To 1 Step -1
        If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
            rng.Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

This code checks rows A2 to C100. If a row is completely empty, it deletes it.

✅ VBA Example 2: Delete Rows Where a Specific Column is Blank (e.g., Column A)

Sub DeleteRowsWhereColumnAIsBlank()
    Dim lastRow As Long
    Dim i As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row

    For i = lastRow To 1 Step -1
        If IsEmpty(Cells(i, 1)) Then
            Rows(i).Delete
        End If
    Next i
End Sub

This script deletes rows where column A is blank. It's useful when only a specific field matters.

✅ VBA Example 3: Delete Rows Where All Columns (A to C) Are Blank

Sub DeleteFullyBlankRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = lastRow To 1 Step -1
        If Application.WorksheetFunction.CountA(ws.Range("A" & i & ":C" & i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i
End Sub

This version checks columns A to C in each row. It deletes only if all are blank.

🧠 Tips for Best Results

  • Always save a backup before running VBA scripts.
  • Consider turning off screen updating in VBA for faster performance:
Application.ScreenUpdating = False
'... your code ...
Application.ScreenUpdating = True
  • Use dynamic ranges or named ranges to handle datasets that change in size.

📊 Final Thoughts

Removing blank rows in Excel doesn't have to be manual. You can use formulas or automate with VBA. Both methods save time and keep data clean.

Learning both methods prepares you for different data-cleaning challenges in Excel.

External Resources for Further Learning

  1. Microsoft Support – FILTER Function
    Official documentation on how to use the FILTER function in Excel to return specific rows based on conditions.

  2. Microsoft Support – COUNTA Function
    Learn how the COUNTA function helps determine if cells or rows are empty or contain data.

  3. Microsoft Learn – Getting Started with VBA in Excel
    A beginner-friendly introduction to writing and running VBA code in Excel.

  4. Ablebits – How to Remove Blank Rows in Excel
    A comprehensive guide covering various methods to remove blank rows, including formulas and built-in Excel tools.

  5. Excel Campus – How to Use VBA to Delete Blank Rows
    Detailed explanation and examples for using VBA to automate row deletion in Excel worksheets.