How to Use VBA to Delete Empty Rows in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Today I’ll show you how you can use VBA to delete the empty rows in Excel from your data set.

Table of Contents hide

How to Use VBA to Delete Empty Rows in Excel: A Step-by-Step Guide

Step 1: Open the VBA Window to Delete Empty Rows in Excel

Step 2: Insert the VBA Module to Delete Empty Rows in Excel

Step 3: Embed the VBA Code to Delete Empty Rows

Step 4: Save the Workbook as a Macro-Enabled Feature to Delete Empty Rows in Excel

Step 5: Select Data from Where You Want to Delete Empty Rows in Excel

Step 6: Run the VBA Macro to Delete Rows with Blank Cells in Excel

Step 7: The Final Output to Delete Empty Rows in Excel

Conclusion

How to Use VBA to Delete Empty Rows in Excel: A Step-by-Step Guide

Here we’ve got a data set with the Names of some students and their Marks in English in the annual examination of a school called Sunflower Kindergarten.

But mistakenly, some empty rows have been created within the data set which we want to delete. In this article, I’ll show you how you can delete the empty rows from this data set using a Macro (VBA Code).

Step 1: Open the VBA Window to Delete Empty Rows in Excel

Press ALT+F11 on your keyboard. The VBA window will open.

Step 2: Insert the VBA Module to Delete Empty Rows in Excel

Go to the Insert tab in the VBA window.
From the options available, select Module.

Step 3: Embed the VBA Code to Delete Empty Rows

A new module window called “Module 1” will open.
Insert the following VBA code in the module.

Code:

Sub Delete_Rows()
Dim Count As Integer
For i = 1 To Selection.Rows.Count
For j = 1 To Selection.Rows.Count
    Count = 0
    For k = 1 To Selection.Columns.Count
        If Selection.Cells(j, k) = "" Then
            Count = Count + 1
        End If
    Next k
    If Count = Selection.Columns.Count Then
        Rows(j + 3).EntireRow.Delete
    End If
Next j
Next i
End Sub

Notes:

This code produces a Macro called Delete_Rows.
In the 12th line of the code, Rows(j + 3).EntireRow.Delete, 3 is the number of rows before the left before the first row of your data set.
You change it according to your data set.