How do I delete multiple BLANK rows within data?

T

The Old Bailey

I extracted a file & converted it to xl format. Every 4 rows there is a BLANK
row dividing the next section/rows of data. I tried using Data > Sort &
Advanced Filter but in the rows of data I need the data is not consistently
entered into each field. So this makes it hard to filter correctly. I need to
filter the data but due to the BLANK rows it is not working.
I have removed some of the blank rows by using Ctrl & delete but this is
very time consuming & the file is huge. Thx
 
J

Jack Schitt

Just a suggestion. Not sure if it will work or even addresses your problem:
Try using autofilter instead of advanced filter. I find that with
autofilter, Excel tries to intelligently identify the range to be filtered,
and doesn't always get it right. Whet it gets it wrong it can be a bit of a
problem forcing it to accept the correct range.
In your case it MAY help if you locate the column at the end of the data,
and then in the next spare column enter some random data in the top cell
then copy it down to the final used row. That should not take too long.
Then make sure that the column next to the right is entirely blank, as is
the row immediately below the last row of data. Conceivably you may want to
do the same thing with the first column to the left (perhaps having to
insert a column to do this). Then the autofilter range may pick up the
whole database. Then filter it on "blank" across all of the columns except
the dummy columns that you have created. Then delete the visible rows, then
delete the dummy columns that you set up for this exercise.
 
R

Ragdyer

Select *all* your data, then,
<Data> <Filter> <AutoFilter>
Click the small down arrow in *any* column,
then click on "blanks".
Right click in this selection, and choose "DeleteRow", then <OK>.
And all your blank rows should be gone.
 
Top