Removing blank cells

[ILunacy]

I have a column with about 500 addresses. There is a blank cell in between
each address. Is there any way to remove all the blank cells at once WITHOUT
sorting the addresses? The addresses aren't in any alphabetical or numerical
order, and I have to keep them in the order they are currently in, So I can't
use the sort function.

Thanks!

 

[Fred Smith]

Use Autofilter. Turn it on, select the blank rows, then delete them.

Regards,
Fred

 

[מיכ×ל (מיקי) ×בידן]

With a little workaround:
Suppose your range ia A1:A10.
a) Select the whole range > press [F5] > 'Special...' > mark 'constants' > OK
b) Press CTRL+C (for copy) > select cell B1 > press CTRL+V.
c) Delete column "A"
C'est tout
Micky

 

[StevenM]

You can add a 'helper' column and number it from 1 to 500 (or however many
rows you have). sort everything by the column with the blanks and they will
group together. delete all the rows you want to get rid of. resort with the
numbered helper column to get it back to the original order and then remover
the helper column.

there may be better ways, but that's one easy way to do it.
S.

 

[pmartglass]

select the column
right click
copy
right click
paste special
select skip blanks

hope this helps

 

[מיכ×ל (מיקי) ×בידן]

Wouldn't this leave him with the SAME layout - meaning with all the previous
empty cells ?!
Micky

 

[excelent]

Select ur data
Hit F5 and click Specials
select Empty cells and hit OK
delete selected rows

"ILunacy" skrev:

 

[pmartglass]

no, buy selecting the skip blank cells it repastes without the blank cells -
thus condensing the information

 

[מיכ×ל (מיקי) ×בידן]

One of our "Excel" software is faulty.
I checked your suggestion 2-3 times (in Excel 2003) before replying.
Maybe 2-3 DC members will be kind enough to check it out...
Micky

 

[excelent]

Micky that right it dosent work (xl2003)

"מיכ×ל (מיקי) ×בידן" skrev:

 

[Gord Dibben]

pmartglass does not understand the "skip blanks" function.

Skip Blanks is to prevent overwrite existing data in the paste area.

Blank cells are still pasted as you have seen Micky.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Select a column.

F5>Special>Blanks>OK

Edit>Delete>Entire Rows>OK


Gord Dibben MS Excel MVP

 

[pmartglass]

thank you for correcting me

One of our "Excel" software is faulty.
I checked your suggestion 2-3 times (in Excel 2003) before replying.
Maybe 2-3 DC members will be kind enough to check it out...
Micky

 

[מיכ×ל (מיקי) ×בידן]

No Problem.
One might not fully understand the question [ happens to me twice a day ;-)
] but in such cases - if somebody presents a different solution, than mine, -
I always bother to check it out and in most cases the those differences
"lighten" my eyes and help to better understand the question.
Micky