Rifat Hassan
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.
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.
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 SubNotes:
- 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.
Step 4: Save the Workbook as Macro-Enabled Feature to Delete Empty Rows in Excel
➤ Save the workbook as Excel Macro-Enabled Workbook.
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).
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.
Step 7: The Final Output to Delete Empty Rows in Excel
➤ You will find empty rows deleted automatically.
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.
