How to Delete Blank Rows Using Excel VBA?

Here is a simple problem, you have a set of data in an excel sheet, but there are many empty rows. What you want to do is delete these empty rows without changing the order of data or disturbing anything else.

If the order of the rows/data did not matter, removing empty rows was simply a matter of sorting the data. By sorting the data, all the empty rows will get together, which you can select and delete.

However, we are not interested in that. We don't want the data to be reordered. What we want excel to do is that when it sees an empty row, it deletes that data and shift the remaining data up.

This task can be done by writing a VBA macro.

1Sub 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

You need to write this macro, then select the rows from which you want to delete the empty rows, and run this macros. Voila! The empty rows are gone.

Instead of making a selection, you can also specify the range of rows using the Range() function.