Can I remove blanks from a range without using sort?

I have a range of cells A1:a10, say, which obtain data from another source.
Some of those cells a3, a6:a8, say, under certain conditions, will be blank.
How do I reorder this range such that the cells containing information are
listed together, removing the blanks? I want to do this using a formula,
rather than filter or sort, as the data, and hence the blank cells, will
change, and I want to perform analysis on the cells containing data.

this is

from one of the newsgroup correspondents

use this code statement

Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete



=================================

Hugh Murfitt <[email protected]> wrote in message
news:[email protected]...
> I have a range of cells A1:a10, say, which obtain data from another
source.
> Some of those cells a3, a6:a8, say, under certain conditions, will be
blank.
> How do I reorder this range such that the cells containing information
are
> listed together, removing the blanks? I want to do this using a formula,
> rather than filter or sort, as the data, and hence the blank cells, will
> change, and I want to perform analysis on the cells containing data.

a macro or
make your selection>f5>special>blanks

--
Don Guillett
SalesAid Software
[email protected]
"Hugh Murfitt" <[email protected]> wrote in message
news:[email protected]...
> I have a range of cells A1:a10, say, which obtain data from another
source.
> Some of those cells a3, a6:a8, say, under certain conditions, will be
blank.
> How do I reorder this range such that the cells containing information
are
> listed together, removing the blanks? I want to do this using a formula,
> rather than filter or sort, as the data, and hence the blank cells, will
> change, and I want to perform analysis on the cells containing data.

I was hoping for a formula that would re-order automatically without losing
data

"R.VENKATARAMAN" wrote:

> this is
>
> from one of the newsgroup correspondents
>
> use this code statement
>
> Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
>
>
>
> =================================
>
> Hugh Murfitt <[email protected]> wrote in message
> news:[email protected]...
> > I have a range of cells A1:a10, say, which obtain data from another
> source.
> > Some of those cells a3, a6:a8, say, under certain conditions, will be
> blank.
> > How do I reorder this range such that the cells containing information
> are
> > listed together, removing the blanks? I want to do this using a formula,
> > rather than filter or sort, as the data, and hence the blank cells, will
> > change, and I want to perform analysis on the cells containing data.
>
>
>

Hugh

What type of analysing are you doing on the data?

Most Functions will ignore blanks in a range.

=SUM(A1:A10) ignores the blanks.

To delete the blanks if desired..........

Select A1:A10 then F5>Special>Blanks>OK

Edit>Delete>Shift Up.


Gord Dibben Excel MVP



On Fri, 4 Mar 2005 03:43:03 -0800, Hugh Murfitt
<[email protected]> wrote:

>I have a range of cells A1:a10, say, which obtain data from another source.
>Some of those cells a3, a6:a8, say, under certain conditions, will be blank.
> How do I reorder this range such that the cells containing information are
>listed together, removing the blanks? I want to do this using a formula,
>rather than filter or sort, as the data, and hence the blank cells, will
>change, and I want to perform analysis on the cells containing data.

{=INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>0,ROW(A$1:A$10),""),ROW(A1:A1)))}
(control-shift-enter) will work if they are really blanks. If the
"blank" values are "" returned from elsewhere, they will remain "". If
0 is returned, they will be removed.
Note that your 1st reference must be to A1. Won't work if you use A2,
for example.

HTH



Hugh Murfitt wrote:
> I have a range of cells A1:a10, say, which obtain data from another source.
> Some of those cells a3, a6:a8, say, under certain conditions, will be blank.
> How do I reorder this range such that the cells containing information are
> listed together, removing the blanks? I want to do this using a formula,
> rather than filter or sort, as the data, and hence the blank cells, will
> change, and I want to perform analysis on the cells containing data.

This is EXACTLY what I wanted. Thanks a million for saving me a huge amount
of time.

"Hugh" wrote:

> {=INDEX(A$1:A$10,SMALL(IF(A$1:A$10<>0,ROW(A$1:A$10),""),ROW(A1:A1)))}
> (control-shift-enter) will work if they are really blanks. If the
> "blank" values are "" returned from elsewhere, they will remain "". If
> 0 is returned, they will be removed.
> Note that your 1st reference must be to A1. Won't work if you use A2,
> for example.
>
> HTH
>
>
>
> Hugh Murfitt wrote:
> > I have a range of cells A1:a10, say, which obtain data from another source.
> > Some of those cells a3, a6:a8, say, under certain conditions, will be blank.
> > How do I reorder this range such that the cells containing information are
> > listed together, removing the blanks? I want to do this using a formula,
> > rather than filter or sort, as the data, and hence the blank cells, will
> > change, and I want to perform analysis on the cells containing data.
>