- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: Loop to Delete All Blank Rows in Excel Range
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 SubThe End Result
