Removing blank rows in a worksheet

G

Guest

Hi all

A colleague of mine has a report in Excel but somebody else has created it
and they have entered a blank line in between each row of data. As the
report is quite big, is there any way Excel will automatically remove any
blank rows without having to select them all?

Any urgent help would be appreciated.

Thank you.


Louise
 
G

Guest

Whatever you do, make sure you have the data backed up

One way: (this works only if EVERY row of data is complete, ie.e, no blanks
in a data row)
Select the entire range of data
Press the F5 key and click on the Special button
Check the Blanks option & click on OK
All blank rows should be selected
Use Edit>Delete>Shift Cells Up

Second way
Insert a new col A
Fill it with numbers down to the bottom of the range (put a 1 in the first
row, select the column down to the bottom of the data range, use
Edit>Fill>Series)
Sort on column B to get all the blank rows together
Delete the blank rows
re-sort on col A
 
G

Guest

Hello and thank you for your reply.

I haven't tried these before so will give it a go, although I'm not quite
sure what you mean by 'this way only if EVERY row of data is complete, ie. no
blanks in a data row'. Every other row on the worksheet is blank??

Thanks again.

Louise
 
G

Guest

I phrased that badly, because you wre clear about the rows being blank. The
issue is whether each row that contains data has an entry in EACH AND EVERY
cell in the range.

It won't work if the first & third DATA rows each have 5 contingous cells
with data, but the second DATA row has entries in only 4 of those cells, and
the other one is blank, as in the example below

Col1 Col2 Col 3 Col4 Col 5
data data data data data

data data data data

data data data data data
 
G

Guest

We tried the first method and it seems to have worked perfectly.

Thanks very much.

Louise
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top