How to Use VBA to Delete Empty Rows in Excel?

VBA Code 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.


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.

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. In this article, I’ll show you how you can delete the empty rows from this data set using a Macro (VBA Code).

Read More: How to Delete Rows Using Excel Shortcuts


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

Read More: How to Delete Multiple Rows in Excel at Once


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

Read More: How to Delete Selected Rows in Excel


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

Read More: Delete All Rows Below a Certain Row in Excel


Similar Readings:


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

  • Save the workbook as an Excel Macro-Enabled Workbook.

Saving Excel Macro-Enabled Workbook in Excel

Related Content: How to Delete Rows in Excel with Specific Text


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

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

Selecting Data Set to Delete Empty Rows in Excel VBA

Related Content: How to Remove Highlighted Rows in Excel


Similar Readings:


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

Related Content: How to Delete Hidden Rows 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


Similar Readings:


Download Practice Workbook


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 Articles

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo