ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Loop to Delete All Blank Rows in Excel Range

Updated on July 10, 2013

Excel can make quick work of almost any task involving data, however, if your data has many blanks it can become a pain. If the data is extensive removing the blanks can be a very time consuming task. A simple VBA macro can make quick work of this.

Consider the following sample data. You want to delete all rows that are entirely blank. You will notice that some rows are missing a phone number but still contain a name. In this example we will retain those.

The secret to deleting blank rows is to find last used row and work your way up.


First save the row number of the last used cell

r = ActiveSheet.Cells.SpecialCells(xlLastCell).Row


Then perform the counta function on the row to determine if is empty

If WorksheetFunction.CountA(Rows(r)) = 0


You should end up with a macro looking something like this:

Sub DeleteBlankRows()

Dim r As Integer

r = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

Do Until r = 0
    If WorksheetFunction.CountA(Rows(r)) = 0 Then
        Rows(r).Delete
    End If
    r = r - 1
Loop

End Sub

The End Result

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.