Deleting Empty Rows

  • Thread starter Thread starter Morris.C
  • Start date Start date
M

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.
 
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
 
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.
 
Back
Top