Removing blank lines in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

gurutech

Thread Starter
Joined
Apr 23, 2004
Messages
2,960
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!
 
Joined
Oct 20, 2004
Messages
7,837
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.
 

gurutech

Thread Starter
Joined
Apr 23, 2004
Messages
2,960
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...
 
Joined
Jul 1, 2005
Messages
8,546
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

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top