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

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 EditDeleteShift 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
EditFillSeries)
Sort on column B to get all the blank rows together
Delete the blank rows
re-sort on col A



"Louise" wrote:

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

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

"Duke Carey" wrote:

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 EditDeleteShift 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
EditFillSeries)
Sort on column B to get all the blank rows together
Delete the blank rows
re-sort on col A



"Louise" wrote:

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

a) click the colume which contains blank row you want to delete
b) ctrl+g (goto)
c) select 'special'
d) check 'blank'
e) ok
f) right click the blank row
g) select 'delete row'


"Louise" ...
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

sorry, should be as follow
a) click the colume which contains blank row you want to delete
b) ctrl+g (goto)
c) select 'special'
d) check 'blank'
e) ok
f) right click the blank cell
g) select 'delete....'
h) select 'row'



"NNNNN" . ..
a) click the colume which contains blank row you want to delete
b) ctrl+g (goto)
c) select 'special'
d) check 'blank'
e) ok
f) right click the blank row
g) select 'delete row'


"Louise" ...
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

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

"Louise" wrote:

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

"Duke Carey" wrote:

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 EditDeleteShift 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
EditFillSeries)
Sort on column B to get all the blank rows together
Delete the blank rows
re-sort on col A



"Louise" wrote:

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

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

Thanks very much.

Louise

"Duke Carey" wrote:

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

"Louise" wrote:

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

"Duke Carey" wrote:

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 EditDeleteShift 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
EditFillSeries)
Sort on column B to get all the blank rows together
Delete the blank rows
re-sort on col A



"Louise" wrote:

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