# Removing blank lines in Excel



## gurutech (Apr 23, 2004)

I have a huge excel file (3 sheets, all filled to the 65535 row limit).

I need to remove all "blank" lines easily, but due to the nature of the data, I can't do a "sort" to bring all the blank lines to one location in the file. Is there an excel feature to do this, or a macro?

Thanks!


----------



## slurpee55 (Oct 20, 2004)

T free add-in for Excel, ASAP Utilities has an easy tool to do so - just go to http://www.asap-utilities.com/ to download it, then you can use "Columns and Rows, remove all empty rows" to do it.
One caveat - if you have some unusual formatting, such as merged cells, that extend over blank cells, you will lose that.


----------



## bomb #21 (Jul 1, 2005)

ASAP.

Column & Rows > Remove All Empty.


----------



## gurutech (Apr 23, 2004)

Will that remove "all" rows AND columns, or just one or the other?

I did find a way to do this, but it took a little work... filter by a column that I know has no values in it, to show only "non-blanks" - so only rows with data in that column will appear. Then it's a copy/paste to a new tab in Excel. Repeat for the other two tabs (copy data to the same "new" tab...)

Then delete the 3 "old" tabs...


----------



## MRdNk (Apr 7, 2007)

Too much data, move it to MS Access. Do you have MS Access?


----------



## bomb #21 (Jul 1, 2005)

gurutech said:


> Will that remove "all" rows AND columns, or just one or the other?


"Remove all empty rows" is "option 1"; "remove all empty columns" is "option 2". Run both to remove both.


----------



## Keebellah (Mar 27, 2008)

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y

Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header

Happy coding


----------

