Delete Blank Rows

[Bill]

Anyone have a macro or suggestion on how to delete all
blank rows in an spreadsheet?

 

[Guest]

When you sort, all the blanks will go to the bottom.

 

[Kevin Stecyk]

Hi Bill,

This is a very commonly asked question.

You can find several solutions in the Google archives.

http://www.google.com/advanced_group_search?q=group:*excel

Here is one solution posted on Jan 1/2000.

Regards,
Kevin
~~~~~~~~~~~~~~~~
This deletes any row that has a blank cell in column A. This should work
for you if that condition means the row is blank. Blank, of course, means
the cell is empty. With a text file, there could be spaces in the cell and
they only look blank.

Sub DeleteRows()
Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
Set rng = rng.SpecialCells(xlBlanks)
rng.EntireRow.Delete
ActiveWorkbook.Close SaveChanges:=True
End Sub

Regards,
Tom Ogilvy
MVP Excel

 

[Kevin Stecyk]

Just a quick correction...Tom's post was Jan 23, 2000.

Regards,
Kevin

 

[Dave Peterson]

One way around it this is to add a helper column that will contain the original
number of the row.

Insert a new column A.
Put 1 in A1
put 2 in A2

Select A1:A2 and drag down your list.

Sort your data to get the blanks at the bottom.

Delete those (almost) blank rows (they now have row numbers).

Resort your data by column A (the original row numbers).

Delete column A.