Delete blank rows from worksheet in VBA Excel – debugvba.com

delete blank rows vba

Objective

To delete blank rows using VBA Excel.

Approach

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.

Code

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
Rows(RowNumber).Delete
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

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.delete

Post you may like

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