Removing blank rows

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
Range("b1").Select
ActiveCell.End(xlDown).Offset(0, -1).Activate
ActiveCell.End(xlUp).Activate
Dim firstrow, counter As Integer
counter = 0
firstrow = 2
Do Until ActiveCell.Row < firstrow
If ActiveCell.Value = Empty Then
ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Activate
counter = counter + 1
Else
ActiveCell.Offset(-1, 0).Activate
End If
Loop
MsgBox "All done! " & counter & _
" rows were removed.", vbExclamation, "Completed"
Range("A1").Select
'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?

neo

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.

Jay

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."
.EntireRow.Delete
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
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
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
Columns("A:A").Insert
Range("A1").Value = "x"
With Intersect(ActiveSheet.UsedRange, Columns("A:A"))
.Formula = "=IF(COUNTA(RC[1]:RC[" & x & "])=0,1,"""")"
.Copy
.PasteSpecial Paste:=xlValues
.EntireRow.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlNo
On Error Resume Next
.SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
End With
Columns("A:A").Delete
x = ActiveSheet.UsedRange.Rows.Count
End Sub