Delete blank rows from worksheet in VBA Excel –

delete blank rows vba


To delete blank rows using VBA Excel.


In this VBA Excel automation, we had 8 sample rows in sheet1, out of which 3 rows were blank. To delete all the blank rows we have looped through each row in sheet1, then for each row we have applied CountA function to find that if that row has any cell which has data in it or not. If the result of CountA function is zero, which means row has no value in any of the cells, then we shall delete the row and move on to next row.


In the below shown code we have used the delete method of range object.

Sub DeleteBlankRows()

Dim LastPopulatedRow As Long
Dim NonEmptyCellCount As Long

'Find last row
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row

'Loop through each row
For RowNumber = LastPopulatedRow To 1 Step -1

'Count the cells which have data
NonEmptyCellCount = Application.WorksheetFunction.CountA(Rows(RowNumber))

'Delete row if there is no populated cell
If NonEmptyCellCount = 0 Then
End If
Next RowNumber

End Sub


In the image below on the left side we can see that we had 3 blanks rows which we want to delete. On the right side of the image we can see that once we execute the below shown code all 3 blank rows will be deleted.


delete blank rows vba



Post you may like

Debug “Runtime error 76: path not found” in VBA Excel –