Delete Blank Rows Excel VBA Macro

Cleaning up and arranging a data set is always tedious job, but Excel macros can do the job for us. In this example we see how to delete blank rows with Excel VBA. It is a simple macro example to understand programming loops and conditional statements. Use the code below to delete blank rows in Excel.

Macro/VBA code:

  

  Sub DeleteBlankRows()

      Dim r As Long, lr As Long

      lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

      Do Until r = lr

          r = r + 1

          If ActiveSheet.Range("A" & r).Value = "" Then

              ActiveSheet.Rows(r).Delete

              r = r - 1

              lr = lr - 1

          End If

      Loop

  End Sub

Macro explained:

• First we declare two Long variables (r and lr) that will hold row numbers.
• The next step is to get the last row with data in column A (lr). That’s done using the 'Row' property of a range or cell combined with the 'End' property and 'xlUp' attribute. In this way, it starts at the last row (Rows.Count) of column A, and goes up until it finds a cell with data in the active sheet.
• The 'Do Until' loop runs until the row counter (r) equals the last cell with data (lr). For the loop to properly work we need to increment the row counter: r=r+1
• An IF statement checks if there is any value in column A for each row. For the first row that would be Range(“A1”).Value, for the second Range(“A2”).Value, etc. Therefore, we use Range(“A” & r).Value.
• If there is no value (nothing between the quotations) it deletes the entire row. Additionally, it deducts a unit to the values of lr and r, as there is one row less in the data set.

This is how we delete blank rows in Excel using VBA macros.

Other examples:

• How to Highlight Duplicates with Excel VBA macros
• How to List Unique Entries with Excel VBA macros
• How to Order Sheets Alphabetically with Excel VBA macros