Excel like a finance pro.

The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.

← All VBA snippets

Delete blank rows in the active sheet

Loops bottom-up (so deletions don't shift the loop) and removes fully empty rows.

Difficulty

Good

Workbook

1What it does

Scans the used rows from the bottom up and deletes any that are completely empty. Looping bottom-up matters: deleting top-down would shift later rows up and skip some. Then it brands what's left — a coloured header row with a lime underline and soft zebra striping (rebrand via the palette up top) — and reports how many rows it removed. Great for cleaning exported or pasted data before analysis.

2The code

Sub RemoveBlankRows()
    ' --- Wauvel palette · swap these for your own brand ---
    Dim cAccent As Long, cPop As Long, cBand As Long
    cAccent = RGB(108, 77, 246)   ' header fill   #6C4DF6
    cPop = RGB(214, 248, 76)      ' accent line   #D6F84C
    cBand = RGB(247, 245, 255)    ' zebra stripe  #F7F5FF

    Dim r As Long, lastRow As Long, lastCol As Long, removed As Long, i As Long
    Application.ScreenUpdating = False

    ' Delete fully empty rows, bottom-up so the loop never skips one.
    lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    For r = lastRow To 1 Step -1
        If Application.CountA(ActiveSheet.Rows(r)) = 0 Then
            ActiveSheet.Rows(r).Delete
            removed = removed + 1
        End If
    Next r

    ' Brand what's left: row 1 = header, then a soft zebra stripe below it.
    lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    lastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    With ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, lastCol))
        .Interior.Color = cAccent
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
        .Borders(xlEdgeBottom).Color = cPop
        .Borders(xlEdgeBottom).Weight = xlMedium
    End With
    For i = 2 To lastRow
        If i Mod 2 = 0 Then _
            ActiveSheet.Range(ActiveSheet.Cells(i, 1), _
                ActiveSheet.Cells(i, lastCol)).Interior.Color = cBand
    Next i

    Application.ScreenUpdating = True
    MsgBox "Removed " & removed & " blank row(s) and tidied the table.", _
        vbInformation, "Wauvel"
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm) — or download the workbook: a branded Excel file with this code on a sheet, sample data to run it on, and a step-by-step setup guide.

New to macros? Set up in 5 minutes▾

1
Don't see the Developer tab in the ribbon?
You don't strictly need it — Alt + F11 opens the editor directly — but it makes running macros easier.
- Windows: File → Options → Customize Ribbon → tick Developer in the right-hand list → OK.
- Mac:Excel → Preferences → Ribbon & Toolbar → tick Developer → Save.
2
Paste in the code
Press Alt + F11 to open the Visual Basic editor, then Insert → Moduleand paste the snippet's code into the blank window. Close it with Alt + Q.
3
Run it
Press Alt + F8, pick the macro's name, and click Run — that's it. (Pasted a custom function instead? Just type it into a cell like any built-in: =GrossMargin(B2, B3).)
4
Keep the macro — save as .xlsm
File → Save As → Excel Macro-Enabled Workbook (.xlsm). A plain .xlsx silently drops the code when you save.
5
Macros blocked?
Click Enable Content on the yellow bar. If you downloaded the file, you may first need to right-click it → Properties → tick Unblock → OK, then reopen.

Heads up: macros can't be undone with Ctrl + Z — save a copy before running one that changes your workbook.

3When you use it

Clean a CSV/export riddled with empty rows.
Tidy data before turning it into a Table or PivotTable.
Collapse gaps left after filtering and deleting.

4See it in action

A simulation — press Run to perform what the macro does to a sample workbook.

1	Jan	$80,000
2
3	Feb	$88,000
4
5	Mar	$96,000

5Pitfalls

Top-down loops skip rows.

Fix: Always loop bottom-up (Step -1), as this code does.

A 'blank' row holds a formula returning "".

Fix: CountA still counts it — clear the formulas first, or test the values you care about.

6No-code alternatives

Go To Special → Blanks — Select blanks (Ctrl+G → Special), then delete the rows — no code.
Filter → delete — Filter to blanks and delete the visible rows.

Rather not write macros? Wauvel's free tools generate branded, formula-driven Excel for you — no VBA required.

Learn the moves here — or let Wauvel run them on your numbers.

Get my free report →