How to eliminate empty rows in Excel 2002 worksheet ?

[Mr. Low]

Dear friends,

I am using Seagate Infordesktop Software to run reports in Excel 2002 format.

My report was designed with an empty row between each data line from top to
bottom.

I need to eliminate those empty row spacing without altering the sequence of
the data lines. May I know is there any shortcut or keyboard command for
doing this ?
Currently

I am sorting block by block of the data to eliminate the rows. I cannot do
it in one go for the entire worksheet asonly the first column of the line of
each block of data has a reference number.

I would be glad if some one who is knowledgable in MS Excel 2002 Spreadsheet
could advise me on this.

Thanks

Low Seng Kuang
Malaysia

 

[Ron Coderre]

Try something like this:

Select a column range in which a blank cell indicates a row to be deleted.
(Example: C5:C1000)

Press the [F5] key......that's the shortcut for <edit><goto>
Click the [Special] button
Check: Blanks
Click the [OK] button

(Now only the blank cells are selected)

From the Excel main menu:
<edit><delete><entire row>

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[ice]

Have you tried to "auto filter" the blanks?

 

[Mr. Low]

Hello Ron,

I tried your steps, It works very well. Many thanks for your help

May I know what are steps for the reverse , i.e. to insert one, two or three
blank rows of cells to lines of data ?

Kind Regards

Low Seng Kuang




--
A36B58K641

Try something like this:

Select a column range in which a blank cell indicates a row to be deleted.
(Example: C5:C1000)

Press the [F5] key......that's the shortcut for <edit><goto>
Click the [Special] button
Check: Blanks
Click the [OK] button

(Now only the blank cells are selected)

From the Excel main menu:
<edit><delete><entire row>

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Dear friends,

I am using Seagate Infordesktop Software to run reports in Excel 2002 format.

My report was designed with an empty row between each data line from top to
bottom.

I need to eliminate those empty row spacing without altering the sequence of
the data lines. May I know is there any shortcut or keyboard command for
doing this ?
Currently

I am sorting block by block of the data to eliminate the rows. I cannot do
it in one go for the entire worksheet asonly the first column of the line of
each block of data has a reference number.

I would be glad if some one who is knowledgable in MS Excel 2002 Spreadsheet
could advise me on this.

Thanks

Low Seng Kuang
Malaysia

 

[Mr. Low]

I know very little about this before

May I know the key board steps ?

Thanks

Low

 

[Ron Coderre]

Here's one way,
using an example of 500 rows of data
and inserting 2 blank rows between each data row:

Insert 1 column before your data
(I'll assume Col_A)

Set the value of A1 to the number 1.
Select from A1 down through the last Col_A cell that corresponds to a data
row.
(In this example, that would be A500)

From the Excel main menu:
<edit><fill><series>Click: [OK]
(Now you'll have a numeric sequence of 1 thru 500)

Put this formula in A501: =A1+0.1
and copy it down thru A1500

Now you'll have 3 sets of numbers in Col_A
The first set will be 1-500 (incrementing by 1)
The second set will be 1.1-500.1 (incrementing by 1)
The third set will be 1.2-500.2 (incrementing by 1)

Select from A1 down thru the last cell of your data

From the Excel main menu:
<data><sort>
Sort by:
Column A, Ascending
Click: [OK]

That should insert 2 blank rows between each data row
Delete Col_A and you're done

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hello Ron,

I tried your steps, It works very well. Many thanks for your help

May I know what are steps for the reverse , i.e. to insert one, two or three
blank rows of cells to lines of data ?

Kind Regards

Low Seng Kuang




--
A36B58K641

Try something like this:

Select a column range in which a blank cell indicates a row to be deleted.
(Example: C5:C1000)

Press the [F5] key......that's the shortcut for <edit><goto>
Click the [Special] button
Check: Blanks
Click the [OK] button

(Now only the blank cells are selected)

From the Excel main menu:
<edit><delete><entire row>

Does that help?
***********
Regards,
Ron

XL2002, WinXP

Dear friends,

I am using Seagate Infordesktop Software to run reports in Excel 2002 format.

My report was designed with an empty row between each data line from top to
bottom.

I need to eliminate those empty row spacing without altering the sequence of
the data lines. May I know is there any shortcut or keyboard command for
doing this ?
Currently

I am sorting block by block of the data to eliminate the rows. I cannot do
it in one go for the entire worksheet asonly the first column of the line of
each block of data has a reference number.

I would be glad if some one who is knowledgable in MS Excel 2002 Spreadsheet
could advise me on this.

Thanks

Low Seng Kuang
Malaysia

 

[Mr. Low]

Hello Ron,

I tried your method today. It worked out very well.

Many thanks for your guidience.


Kind Regards

Low Seng Kuang
Malaysia


--
A36B58K641

Here's one way,
using an example of 500 rows of data
and inserting 2 blank rows between each data row:

Insert 1 column before your data
(I'll assume Col_A)

Set the value of A1 to the number 1.
Select from A1 down through the last Col_A cell that corresponds to a data
row.
(In this example, that would be A500)

From the Excel main menu:
<edit><fill><series>Click: [OK]
(Now you'll have a numeric sequence of 1 thru 500)

Put this formula in A501: =A1+0.1
and copy it down thru A1500

Now you'll have 3 sets of numbers in Col_A
The first set will be 1-500 (incrementing by 1)
The second set will be 1.1-500.1 (incrementing by 1)
The third set will be 1.2-500.2 (incrementing by 1)

Select from A1 down thru the last cell of your data

From the Excel main menu:
<data><sort>
Sort by:
Column A, Ascending
Click: [OK]

That should insert 2 blank rows between each data row
Delete Col_A and you're done

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

Hello Ron,

I tried your steps, It works very well. Many thanks for your help

May I know what are steps for the reverse , i.e. to insert one, two or three
blank rows of cells to lines of data ?

Kind Regards

Low Seng Kuang




--
A36B58K641

Try something like this:

Select a column range in which a blank cell indicates a row to be deleted.
(Example: C5:C1000)

Press the [F5] key......that's the shortcut for <edit><goto>
Click the [Special] button
Check: Blanks
Click the [OK] button

(Now only the blank cells are selected)

From the Excel main menu:
<edit><delete><entire row>

Does that help?
***********
Regards,
Ron

XL2002, WinXP


:

Dear friends,

I am using Seagate Infordesktop Software to run reports in Excel 2002 format.

My report was designed with an empty row between each data line from top to
bottom.

I need to eliminate those empty row spacing without altering the sequence of
the data lines. May I know is there any shortcut or keyboard command for
doing this ?
Currently

I am sorting block by block of the data to eliminate the rows. I cannot do
it in one go for the entire worksheet asonly the first column of the line of
each block of data has a reference number.

I would be glad if some one who is knowledgable in MS Excel 2002 Spreadsheet
could advise me on this.

Thanks

Low Seng Kuang
Malaysia

 

[ice]

If you are still here,

then go to the top row select the entire row and go to
"Data" - Filter - Auto filter and check it.
Some arrows will appear to the first row. Go to one of them and open it.
Find "blanks" and then you will have infront of you the blank rows.

mark them all and delete them.
Go to filter again and select "all"

Hope it helps.