Excel 2002 worksheet

[cmd661]

How can I delete ALL empty rows at once? Can it be done?

[Gary''s Student]

Try this macro:

Sub Macro1()
Dim j As Long
Dim i As Long
Dim r As Range

j = 65536
For i = 1 To j
If Application.CountA(Rows(i)) = 0 Then
If r Is Nothing Then
Set r = Rows(i)
Else
Set r = Union(r, Rows(i))
End If
End If
Next i

If Not r Is Nothing Then
r.Delete
End If
End Sub
--
Gary''s Student


"cmd661" wrote:

> How can I delete ALL empty rows at once? Can it be done?

[Roger Govier]

Hi

One way
Rather than deleting, you could sort the data, and all empty rows would
fall below your data.
If existing sort order is important, then before sorting, use a spare
column adjacent to your data to number the rows.
Fill in 1 in row 1, 2 in row 2, mark both cells and double click the
fill handle to fill down (the fill handle is the small black cross that
appears if you hovver overe the bottom right of the pair of marked
cells)

Sort your data on a column other than this new column, and all the empty
rows (other than the number you have inserted) will fall to the bottom.
Highlight this block of rows at the bottom of your sheet and delete
them.
Then sort by the column with the numbers, to get the file back into
original sort order.
Delete added column if required.

--
Regards

Roger Govier



cmd661 wrote:
> How can I delete ALL empty rows at once? Can it be done?