How to Use VBA to Delete Empty Rows in Excel

VBA Code to Delete Empty Rows in Excel

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


Download Practice Workbook


Delete Empty Rows in Excel Using VBA

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.

Data Set to Delete Empty Rows in Excel VBA

But mistakenly, some empty rows have been created within the data set which we want to delete.

Today 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.

Opening VBA Window in Excel

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.

Inserting New Module in VBA Window

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.

VBA Code to Delete Empty Rows in Excel

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

Save the workbook as Excel Macro-Enabled Workbook.

Saving Excel Macro-Enabled Workbook in Excel

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

Return to your worksheet select the data set (Without the Column Headers).

Selecting Data Set to Delete Empty Rows in Excel VBA

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

➤ Press ALT+F8 on your keyboard.

➤ A dialogue box called Macro will open. Select Delete_Rows and click on Run.

Macro Dialogue Box in Excel

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

You will find empty rows deleted automatically.

Empty Rows in Excel Deleted Using VBA


Conclusion

Using these steps, you can delete the empty rows from your data set in Excel. Do you have any questions? Feel free to ask us.


Related Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo