Useful Hacks, Reviews, Tips and Tricks of anything under the moon and the sun.

Delete blank rows in Excel Workbook using VBA Macro

1 comment
Another VBA macro request for the day. A fan from Plurk private message me about her problem.

Jenny
Hi there! I need an office hack to delete blank rows on my existing workbook. It has over 1000 rows. I just can't go through each line. It could take me days to do that. Do you have any suggestion?


foxtrot

Yes jenny. I have a suggestion...


To delete blank rows in excel workbook using VBA Macro.

1. Open your excel workbook.
2. Open the VBA Editor by going to Tools > Macros > Visual Basic Editor.
3. Now click the ThisWorkbook icon on the left pane and paste the following code on the blank space at the right window.


Sub DeleteBlankRows1()
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


4. Now switch to your workbook window (don't close the VBA editor). Select the range you want to remove blanks.

Delete blank rows in Excel Workbook using VBA Macro

5. Then switch to VBA editor and click the play button on the toolbar or go to Run > Run Sub/User Form

1 comment :