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.
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.
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.
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.
Read More: Delete All Rows Below a Certain Row in Excel
Similar Readings:
- How to Delete Row If Cell Is Blank in Excel
- How to Delete Empty Rows at Bottom in Excel
- How to Delete Multiple Rows in Excel Using Formula
- How to Delete Multiple Rows in Excel with Condition
- How to Delete Rows in Excel without Affecting Formulas
- How to Delete Rows in Excel That Go on Forever
- How to Delete Infinite Rows in Excel
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.
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).
Related Content: How to Remove Highlighted Rows in Excel
Similar Readings:
- How to Delete All Rows Not Containing Certain Text in Excel
- How to Delete Rows Based on Another List in Excel
- How to Remove Rows Containing Identical Transactions in Excel
- [Fixed!] Not Enough Memory to Delete Rows Error in Excel
- How to Delete Row Using VBA
- How to Delete Row with VBA Macro If Cell Is Blank in Excel
- How to Delete Entire Row Based on Cell Value Using VBA in Excel
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.
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.
Similar Readings:
- How to Delete Row If Cell Contains Value Using Macro in Excel
- How to Delete Row Using Macro If Cell Contains 0 in Excel
- VBA to Delete Every Other Row in Excel
- How to Delete Multiple Rows with VBA in Excel
- How to Delete Rows in a Range with VBA in Excel
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
- Applying VBA Code to Delete Rows Based on Multiple Cell Value
- Excel VBA to Delete Rows with Specific Data
- How to Delete Selected Rows with Excel VBA
- How to Use Macro To Delete Rows Based on Criteria in Excel
- How to Filter and Delete Rows with VBA in Excel
- How to Delete Unfiltered Rows in Excel Using VBA
- How to Delete Hidden Rows in Excel VBA
- How to Delete Filtered Rows in Excel
- How to Delete Unused Rows in Excel
- How to Find and Delete Rows in Excel
- How to Delete Every Other Row in Excel
- How to Delete Blank Rows in Excel
- How to Delete Row If Cell Contains Specific Values in Excel