Naming a non-blank range from a col containing non-consec. blank c

[Guest]

Excel 2007. I have a column of data containing several blank cells. These
blank cells are spread out all through the column. I want to define a named
range that contains only the non-blank cells of this column.

I have seen a previous post of how to do this using an array formula, but
the solution not only looks cumbersome but doesn't quite appear to do what
I'm looking for anyway. This solution was found at
cpearson.com/excel/noblanks.htm

Does anyone know of a cleaner solution than this?

 

[Guest]

An alternative play using non-array formulas ..

Assume the data (with interspersed blanks) is in Sheet1, in A1 down

In B1:
=IF(A1="","",ROW())

In C1:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Select B1:C1, copy down to cover the max expected extent of data in col A,
eg down to C200. Col C returns what's in col A but wo blanks.

Then just define a dynamic named range, eg: MyRange
to point to col C, via: Insert > Name > Define with settings:

Names in workbook:
MyRange

Refers to:
=OFFSET(Sheet1!$C:$C,,,SUMPRODUCT(--(Sheet1!$C$1:$C$200<>"")))

(Adapt the range $C$1:$C$200 to suit the max expected extent)

Above works in xl2003 (my ver). I don't know/have xl2007, but think it
should work just as well <g>

 

[Guest]

Max,

Brilliant! Thanks a ton.

- fruitticher

An alternative play using non-array formulas ..

Assume the data (with interspersed blanks) is in Sheet1, in A1 down

In B1:
=IF(A1="","",ROW())

In C1:
=IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Select B1:C1, copy down to cover the max expected extent of data in col A,
eg down to C200. Col C returns what's in col A but wo blanks.

Then just define a dynamic named range, eg: MyRange
to point to col C, via: Insert > Name > Define with settings:

Names in workbook:
MyRange

Refers to:
=OFFSET(Sheet1!$C:$C,,,SUMPRODUCT(--(Sheet1!$C$1:$C$200<>"")))

(Adapt the range $C$1:$C$200 to suit the max expected extent)

Above works in xl2003 (my ver). I don't know/have xl2007, but think it
should work just as well <g>

 

[Max]

welcome. good to hear that.