Excel (Eliminate blank rows)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in a worksheet is there way to automatically elimate all the blank rows in a
selected range
 
Try this:

Select the range

Press the [F5] key....that's the shortcut for <edit><go to>
[alt] S.......to select the [special] button
Press the [K] key....to check: Blanks
Press [enter]

Now all the blank cells are selected

[alt] E.......for the Edit menu
[D] ...........for delete
[R]............for entire rows
Press [enter]

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
In column A, select the rows you want to process and then run:

Sub delete_empty_rows()

Dim r As Range, j As Long

Set r = ActiveSheet.UsedRange
j = r.Rows.Count + r.Row
Set rdel = Cells(j, "A")

i = Selection.Cells(1, 1).Row
j = Selection.Offset(Selection.Count - 1, 0).Row

For i = 1 To j
If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then
Set rdel = Union(rdel, Cells(i, "A"))
End If
Next

rdel.EntireRow.Delete

End Sub
 
Back
Top