How to Delete Blank Rows in Excel Using VBA (Step-by-Step Guide)

When you work with large datasets in Excel, it’s common to end up with thousands of blank rows scattered throughout your worksheet. Maybe your data came from a mainframe export, a CSV download, or a legacy system that doesn’t format properly. Deleting all those empty rows manually can take hours — but with a few lines of VBA (Visual Basic for Applications), you can remove them instantly.

In this tutorial, we’ll show you how to delete blank rows in Excel using VBA — the same method demonstrated in the video here:

In the video you’ll learn how to identify empty rows, write a simple macro, and automate the cleanup process to keep your spreadsheets fast, accurate, and professional.

🧠 Why Blank Rows Are a Problem

Blank rows can cause more trouble than you might think. They interrupt formulas, confuse data sorting, and prevent functions like VLOOKUP, SUMIFS, and FILTER from returning accurate results. When you import or merge data, Excel might misread the blank rows as record breaks — creating false totals or extra spacing in reports.

Instead of manually scrolling and deleting, VBA lets you automate the cleanup with a single command.

⚙️ Step 1: Open the VBA Editor

To begin, open your Excel file and press Alt + F11 to launch the VBA Editor. This is where you’ll write your macro code.

1. Go to Insert > Module.
2. This creates a new code window where you can paste your VBA script.

💻 Step 2: Add the VBA Code to Delete Blank Rows

Here’s a simple, efficient macro that deletes blank rows in column A (you can easily modify it for any column):

Sub DeleteBlankRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range

    Set ws = ActiveSheet
    On Error Resume Next
    Set rng = ws.Range("A1", ws.Cells(ws.Rows.Count, "A").End(xlUp))
    On Error GoTo 0

    For Each cell In rng
        If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
            cell.EntireRow.Delete
        End If
    Next cell
End Sub

Tip: If your data has blank cells scattered across multiple columns, change the logic to check the entire row rather than just one column.

🚀 Step 3: Run the Macro

After pasting the code, return to Excel and press Alt + F8 to open the Macro dialog. Select DeleteBlankRows and click Run.

Excel will process your worksheet and automatically delete every blank row. Depending on your dataset size, this might take a few seconds. In the demonstration, the macro processed over 15,000 rows in just moments.

⚡ Step 4: Verify Your Data

To confirm the cleanup worked:

1. Click on a cell in column A.
2. Press Ctrl + ↓ to jump to the bottom of your data.
3. You’ll see that there are no gaps — all blank rows have been removed.

Your spreadsheet should now be clean, compact, and ready for formulas or reporting.

🧩 Where to Get the Code Snippet

You can grab a copy of this ready-to-use VBA code from the VBA Answers page on this site. Just click Copy, open your workbook, and paste the code directly into your VBA editor. The site also includes dozens of similar code snippets for automating Excel — from removing duplicates to copying data between sheets.

🧭 Bonus: Faster Methods for Large Datasets

If you’re dealing with extremely large files (100,000+ rows), you can speed up the process with these quick optimizations:

• Turn off screen updating: Add Application.ScreenUpdating = False at the top of your macro and set it back to True at the end.
• Use Range.SpecialCells: This method skips looping altogether:

Sub DeleteBlanksFast()
    On Error Resume Next
    Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

This deletes all blank rows in column A almost instantly.

✅ Final Thoughts

Deleting blank rows manually is tedious and error-prone. By learning how to use Excel VBA, you can handle repetitive cleanup tasks in seconds — saving time and ensuring accuracy. Once you understand this basic concept, you’ll be ready to automate even more data-handling processes in Excel.

If you found this tutorial helpful, be sure to subscribe to the VBAHowTo YouTube channel and visit VBAHowTo.com for hundreds of free code examples and step-by-step VBA lessons.

🔑 SEO Keywords

delete blank excel rows vba, remove empty rows excel, excel vba macro tutorial, vba delete empty rows, excel automation tips, excel vba examples, how to clean excel data with vba, delete blank rows macro, excel clean data automation, vbahowto

By the way, if you got or are getting value from the VBA information, please click the "Donate" button to give me a small token of your appreciation, thanks!