how do you do this?

[littledrew]

lets say i have a single column group of cells... theres 20 cells and most of them are blank and a few have names.

how could i use index/array or whatever function to relist those names ( and not the blank spaces) in another area ( for aesthetic purposes) ??

thanks

 

[Alisa]

copy the range
then paste special and check the box "skip blanks"
if you are using it in a macro, its:

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= True

 

[Bubbis Thedog]

You could select the upper cell in the range, select Data-->Filter-->Autofilter. Then you could select NonBlanks from the list.

If this is not sufficient, tell us.

Bubbis

 

[Aladin Akyurek]

lets say i have a single column group of cells... theres 20 cells and most of them are blank and a few have names.

how could i use index/array or whatever function to relist those names ( and not the blank spaces) in another area ( for aesthetic purposes) ??

thanks

If you insist on using a formula system...

Let A2:A23 house the data, including a label in A2.

In B2 enter: 0

which is needed.

In B3 enter & copy down:

=IF((A3<>""),LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B3:B23)

In C2 enter: New List

In C3 enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,$B$3:$B$23,$A$3:$A$23),"")

 

[littledrew]

wow... that worked perfectly...wow...i'll try to analyze the code myself... thanks, this great