Tags: dear, designed, eliminate, empty, excel, format, friends, infordesktop, microsoft, msdn, report, reports, row, rows, run, seagate, software, worksheet
How to eliminate empty rows in Excel 2002 worksheet ?
On Microsoft » Microsoft Excel
15,638 words with 7 Comments; publish: Thu, 22 May 2008 09:24:00 GMT; (30662.50, « »)
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
--
A36B58K641
http://excel.itags.org/q_microsoft-excel_243483.html
All Comments
Leave a comment...
- 7 Comments
- 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
"Mr. Low" wrote:
> 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
> --
> A36B58K641
#1; Thu, 22 May 2008 09:26:00 GMT
- Have you tried to "auto filter" the blanks?
"Mr. Low" wrote:
> 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
> --
> A36B58K641
#2; Thu, 22 May 2008 09:26:00 GMT
- 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
"Ron Coderre" wrote:
> 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
>
> "Mr. Low" wrote:
> > 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
> > --
> > A36B58K641
#3; Thu, 22 May 2008 09:27:00 GMT
- I know very little about this before
May I know the key board steps ?
Thanks
Low
--
A36B58K641
"ice" wrote:
> Have you tried to "auto filter" the blanks?
> "Mr. Low" wrote:
> > 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
> > --
> > A36B58K641
#4; Thu, 22 May 2008 09:29:00 GMT
- 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
"Mr. Low" wrote:
> 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
>
> "Ron Coderre" wrote:
> > 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
> >
> >
> > "Mr. Low" wrote:
> >
> > > 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
> > > --
> > > A36B58K641
#5; Thu, 22 May 2008 09:30:00 GMT
- Hello Ron,
I tried your method today. It worked out very well.
Many thanks for your guidience.
Kind Regards
Low Seng Kuang
Malaysia
A36B58K641
"Ron Coderre" wrote:
> 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
>
> "Mr. Low" wrote:
> > 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
> >
> >
> > "Ron Coderre" wrote:
> >
> > > 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
> > >
> > >
> > > "Mr. Low" wrote:
> > >
> > > > 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
> > > > --
> > > > A36B58K641
#6; Thu, 22 May 2008 09:31:00 GMT
- 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.
"Mr. Low" wrote:
> I know very little about this before
> May I know the key board steps ?
> Thanks
> Low
> --
> A36B58K641
>
> "ice" wrote:
> > Have you tried to "auto filter" the blanks?
> >
> > "Mr. Low" wrote:
> >
> > > 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
> > > --
> > > A36B58K641
#7; Thu, 22 May 2008 09:32:00 GMT