Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 26th 05, 11:27 AM
Louise
 
Posts: n/a
Default Removing blank rows in a worksheet

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

  #2   Report Post  
Old May 26th 05, 12:42 PM
Duke Carey
 
Posts: n/a
Default

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

  #3   Report Post  
Old May 26th 05, 01:12 PM
Louise
 
Posts: n/a
Default

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

  #4   Report Post  
Old May 26th 05, 01:46 PM
NNNNN
 
Posts: n/a
Default

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



  #5   Report Post  
Old May 26th 05, 01:48 PM
NNNNN
 
Posts: n/a
Default

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







  #6   Report Post  
Old May 26th 05, 02:17 PM
Duke Carey
 
Posts: n/a
Default

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

  #7   Report Post  
Old May 26th 05, 02:21 PM
Louise
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hiding Rows if the linked rows are blank KG Excel Discussion (Misc queries) 9 May 18th 05 02:32 AM
how do I print grid lines for blank rows gailrolfe Excel Discussion (Misc queries) 3 February 25th 05 05:22 AM
how do I print grid lines for blank rows calrolfe Excel Discussion (Misc queries) 0 February 24th 05 07:33 PM
Activate a macro to insert a row and copy the formuals from the rows above to the blank row oil_driller Excel Discussion (Misc queries) 1 February 11th 05 03:30 PM
Display specific rows from table on other worksheet Bruno G. Excel Discussion (Misc queries) 2 January 20th 05 11:22 PM


All times are GMT +1. The time now is 07:12 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017