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 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.
Post you may like