Editing A Basic Excel Spreadsheet

[andy-hughes]

I have created a spreadsheet with about three thousand lines on it.

After editing, I have a couple of hundred blank lines.

Is there a way of taking out all the blank lines simultaneously, or must I go through and delete them line by line?

[fiction-factory]

Can you sort the data somehow- eg alpabetically or in number order. That will put all blank lines at athe top or at the bottom, They can then be deleted easily

[bednobs]

go to sort and filter and put a filter on. then filter the blanks and delete them

[ToraToraTora]

just sort it descending then all the spaces will go to the bottom.

[Kidas]

In that situation, I quite often put in an extra column at the start, numbered from 1 .. n (A1=1; A2=1+A1; fill down to bottom, then copy that column and paste-special as values).

Then sort on whatever terms I like to adjust the rest of the table and finally sort on column A again,to restore the original order

[Quoi]

If you want the report to end up in the same order as when you started...
create a column to the left and insert a numbers for each line 1..2..3 etc up to 3000
Use Data..Sort (whole sheet) to get the space lines together to make it easy to delete them.
Then Data..Sort the sheet again using the numbered column A
Finally, delete column A

[Quoi]

Snap... I just took longer typing lol

[fiction-factory]

Maybe I'm misunderstanding the replies that suggest numbering the lines 1-3000 then sorting them. Wouldn't that allocate numbers to the blank lines- eg lines 72, 79, 93, 122 etc which wouldn't help. It's far easier to sort the data you have, say one column alphabtically, then delete the blank rows which will all appear together, then remove the sort if you don't need it. Takes seconds

[andy-hughes]

I used to know that!

It must be old age!!

Thanks guys!!!

[Jiafengyong]

I have another answer. I must install an English version of Excel.

[Jiafengyong]

Firstly, select a column by pressing A or B or C... after that, in the HOME tab, click "Find & Select", "go to special", "Blanks","OK", then right click any selected blank cell, select "Delete", "Entire row","OK".
(I didn't know that answers cannot be reedited, not like Baidu Zhidao in China, in which we can past pictures)

[Flonska]

If the rows are "consecutive" you can click on the first "blue no" to the left of Col A. Hold the shift key down and then click on the last "blue no." This will highlight all the rows. Then click on the delete icon in the tool bar and select" delete sheet rows." If you make a mistake, immediately press Ctrl + Z (undo).