I've got a spreadsheet 4 columns by about 50,000 rows (Excel 2007). Within this range every few rows or so, the fourth column is blank. I need a way to delete all of those rows please. Doing it by hand sucks and I don't know any vb.

EX:

A B C D
1 q t u i
2 d r g
3 f c q e
4 q d h e
5 p q t
6 q a t i

So I need to delete rows 2 & 5. And no, there isn't always the same number of correct rows between rows that need to be deleted.

Filter won't work either, since the cells aren't actually deleted


[Edited on May 28, 2009 at 3:57 PM. Reason : format]

5/28/2009 3:56:55 PM

- Turn on AutoFilter
- Use AutoFilter to sort on column D (sort ascending, descending, whatever)
- then all the rows with blank column D's will be grouped together. Select and delete all of them
- use AutoFilter to sort on column A to put everything back in the original order
- Turn off AutoFilter

---

or just use the Sort functionality to do the same thing, without using AutoFilter

[Edited on May 28, 2009 at 4:01 PM. Reason : .]

5/28/2009 4:00:45 PM

http://www.mvps.org/dmcritchie/excel/delempty.htm

5/28/2009 4:03:25 PM

whoops - i mistook your row numbers (1, 2, 3) as actual values in column A.

no matter - the method I described above still works. But before you sort by column D, add another column with just sequential numbers. e.g. in Column E, just put 1, 2, 3, .... and fill all the way down.
Then sort by column D, delete the empty rows, then sort by column E to get back to the original order, then delete column E

5/28/2009 4:08:09 PM

^sweet! thanks. can't believe I didn't think of sorting it... though I wouldn't have though of numbering them to get it back in the correct order.

thanks again!

[Edited on May 28, 2009 at 4:32 PM. Reason : went through 4 oh shits to post that. I wish nael would show up sometime]

5/28/2009 4:32:15 PM