MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing blank rows

Posted by Pradeep on August 25, 2001 10:50 AM

How may I remove blank rows in a worksheet which contains many large amount of data, as well as blank rows. In some parts the blank rows are appearing alternately in a pattern and other times randomly.

OS : Windows 98
Excel 2000

Thanks for your help

Posted by neo on August 25, 2001 11:18 AM

what you need is...

are you familiar with the VBE? if so, this is what you'll need to do... put a command button on the sheet you wish to remove the rows from. right-click it and go into it's 'view code' module. in that module cut and paste in the following code:

Private Sub CommandButton1_Click()
'Application.ScreenUpdating = False
ActiveCell.End(xlDown).Offset(0, -1).Activate
Dim firstrow, counter As Integer
counter = 0
firstrow = 2
Do Until ActiveCell.Row < firstrow
If ActiveCell.Value = Empty Then
ActiveCell.Offset(-1, 0).Activate
counter = counter + 1
ActiveCell.Offset(-1, 0).Activate
End If
MsgBox "All done! " & counter & _
" rows were removed.", vbExclamation, "Completed"
'Application.ScreenUpdating = True
End Sub

this code is examining column A, starting at the bottom, for blank rows, then deleting them. it will run up to row 1 where it'll stop and tell you how many rows were deleted...

that what you're looking for?


Posted by Jay Deitch on August 25, 2001 6:40 PM

You could also try "Go To... Special" - my favorite utility in Excel. Assuming The cells in column "A" are blank for the rows you want to delete, try this;

1) select column "A"
2) select "Go To..." from the Edit menu
3) click on the "Special..." button
4) select the "Blanks" radio button and click on "OK"

You'll see that the blank cells in Column A are selected.

5) select "Delete" from the Edit menu.
6) select the "Entire Row" radio button and click "OK"

You'll find lots of uses for Go To...Special.


Posted by Michel on August 25, 2001 6:49 PM

Re: what you need is...

It would be better if the first 4 lines of code were substituted with:-

Dim lastrow As Integer
lastrow = ActiveSheet.UsedRange.Rows.Count
Cells(lastrow, 1).Select
Dim firstrow As Integer, counter As Integer

Here's an alternative macro (should be quicker) :-

Sub DeleteBlanks_ColumnA()
Dim x As Integer
x = ActiveSheet.UsedRange.Rows.Count
On Error GoTo e
With Columns("A:A").SpecialCells(xlCellTypeBlanks)
MsgBox .Count & _
" rows to be removed."
End With
Exit Sub
e: MsgBox "There are no rows to delete."
End Sub

Or, if the message about the number of rows deleted is not required :-

Sub DeleteBlanks_ColumnA()
On Error GoTo e
Exit Sub
e: MsgBox "There are no rows to delete."
End Sub

However, the above macros delete rows when column A contains blank cells. If it is required to delete rows only when all cells in the row are blank, here's one way :-

Sub DeleteBlankRows()
Dim x As Integer
x = ActiveSheet.UsedRange.Columns.Count - 1
Application.ScreenUpdating = False
Range("A1").Value = "x"
With Intersect(ActiveSheet.UsedRange, Columns("A:A"))
.Formula = "=IF(COUNTA(RC[1]:RC[" & x & "])=0,1,"""")"
.PasteSpecial Paste:=xlValues
.EntireRow.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo
On Error Resume Next
.SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
End With
x = ActiveSheet.UsedRange.Rows.Count
End Sub