Deleting Empty Rows

[Morris.C]

I need a macro that will delete all rows that are empty.

I cut and pasted data I received in a text file.
The problem was that the lines in the text file were seperated by a blank
line.
I would like to be able to delete all these empty rows. (There's about
10,000 rows, half of which are empty!)

Can anyone help with this macro.

Thanks.

[Gord Dibben]

Morris

You don't need a macro.

Select all by CTRL + a(twice in 2003) and F5>Special>Blanks>OK

Edit>Delete>Entire row

But a macro can be used if you wish.

Public Sub DeleteRowOnCell()
''delete any row that has a blank in selected column(s)
Set coltocheck = Application.InputBox(prompt:= _
"Select A Column", Type:=8)
coltocheck.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


Gord Dibben Excel MVP

On Tue, 12 Apr 2005 22:50:22 +0000 (UTC), "Morris.C" <[email protected]> wrote:

>I need a macro that will delete all rows that are empty.
>
>I cut and pasted data I received in a text file.
>The problem was that the lines in the text file were seperated by a blank
>line.
>I would like to be able to delete all these empty rows. (There's about
>10,000 rows, half of which are empty!)
>
>Can anyone help with this macro.
>
>Thanks.

[Wild Jim]

You don't necessarily need a macro:

Depending on the job you are doing, why not just sort the data on a
particular field? All the blanks will then fall out naturally.

If you want to keep the data in the same order then number all the rows of
data (blanks as well) in a new column first. Then do the sort on one of the
columns, then re-sort just your data (excluding blanks) back into number
order.

A bit quicker than a macro which would read and delete each row meeting the
conditions.

"Morris.C" <[email protected]> wrote in message
news:[email protected]...
> I need a macro that will delete all rows that are empty.
>
> I cut and pasted data I received in a text file.
> The problem was that the lines in the text file were seperated by a blank
> line.
> I would like to be able to delete all these empty rows. (There's about
> 10,000 rows, half of which are empty!)
>
> Can anyone help with this macro.
>
> Thanks.