How to Remove Blank Lines in Excel

Method 1: Find & Replace (Go To Special)

This is the method most people search for, and it doesn't require touching a single formula or macro.

1. Select the range containing your data (or select the whole sheet with Ctrl+A if you're not sure of the exact bounds).
2. Press Ctrl+G to open "Go To," then click "Special..." (or use the Home tab → Find & Select → Go To Special).
3. Choose "Blanks" and click OK. Excel selects every empty cell within your original selection.
4. Right-click any of the selected cells → Delete... → "Entire row" → OK.

One thing worth knowing before you do this: "Go To Special > Blanks" selects empty cells, not necessarily entirely blank rows. If a row has some cells filled and others empty, those empty cells get selected too, and deleting "entire row" will remove rows that aren't actually fully blank - just partially. For data where every row is either completely empty or completely filled, this isn't a problem. For messier data, check your selection in the Name Box (top-left) before deleting, or use a helper column with a COUNTA formula to flag truly blank rows first, then filter and delete based on that instead.

Method 2: VBA macro (for repeated use or larger datasets)

If you're cleaning up blank rows regularly, or working with a dataset too large to comfortably eyeball before deleting, a macro is faster and more precise:

Sub DeleteBlankRows()
    Dim i As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = lastRow To 1 Step -1
        If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
            Rows(i).Delete
        End If
    Next i
End Sub

What this does: it finds the last used row in column A, then loops from that row backwards up to row 1 (For i = lastRow To 1 Step -1), checking each row withCountA - a function that counts non-empty cells. If a row has zero non-empty cells (= 0), it's genuinely blank across every column, and the macro deletes it.

The pitfall worth knowing: if you loop top-to-bottom instead and delete a row as you go, every row below the one you just deleted shifts up by one - but your loop counter keeps incrementing as if nothing moved. The result is that you silently skip the row immediately after every deletion, leaving some blank rows behind. Looping backwards (bottom-to-top, as this macro does) sidesteps the problem entirely: deleting row 50 only affects rows below 50, which your loop has already passed, so nothing gets skipped.

To use it: open the VBA editor (Alt+F11), insert a new module (Insert → Module), paste the code in, close the editor, then run it from Excel via Alt+F8 → select DeleteBlankRows → Run.

Skip Excel entirely

If your data started as pasted or copied text before it became a spreadsheet - a list of names, a CSV export, log lines, anything that isn't inherently tabular - you might not need Excel for this step at all. Remove Blank Lines does the same cleanup on raw text directly in your browser, before you even paste it into a spreadsheet.

This guide is part of our full roundup offree tools for cleaning up messy text, covering duplicate lines, blank lines, extra spaces, and dirty CSV exports.

Frequently Asked Questions