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!

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

Regards,
Fred

"ILunacy" wrote in message
...
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!

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


"ILunacy" wrote:

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!

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.

"ILunacy" wrote:

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!

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

hope this helps

"ILunacy" wrote:

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!

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!

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

"ILunacy" skrev:

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!

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

"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!

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


"pmartglass" wrote:

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

"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!

Micky that right it dosent work (xl2003)

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

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


"pmartglass" wrote:

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

"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!

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

On Fri, 5 Mar 2010 10:22:01 -0800, ????? (????) ?????
<micky-a*at*tapuz.co.il wrote:

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


"pmartglass" wrote:

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

"????? (????) ?????" wrote:

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!

Select a column.

F5SpecialBlanksOK

EditDeleteEntire RowsOK


Gord Dibben MS Excel MVP

On Fri, 5 Mar 2010 08:39:01 -0800, ILunacy
wrote:

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!

thank you for correcting me


"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!

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


"pmartglass" wrote:

thank you for correcting me


"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

"מיכ�ל (מיקי) �בידן" wrote:

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


"pmartglass" wrote:

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

hope this helps

"ILunacy" wrote:

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!