For a Limited Period!
Buy 2 ~ Free 1 pax OR Get 30% off for every pax on our scheduled classesAnalyzing Data with Power BI Microsoft Excel - Power Query (M365, 2021-2010) Microsoft Excel - Power Pivot (M365, 2021-2010) Please This email address is being protected from spambots. You need JavaScript enabled to view it. for more info. |
![]() |
VBA - Deleting Empty Rows* 27/10/2001
To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".