# removing blank cells in a list

#### dirtyh2o

##### New Member
Hi, I have a list of data and blank cellls and I would like to make another list that removes the blanks so all that is left is the data. Any idea how to do this?

Thanks!

edit: I'd like this process to be dynamic (I know I could do it by sorting the list using data sort, but that will wind up screwing things up for me down the line)

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### steve1262

##### Board Regular

So if Cell 5 on Row 5 is blank. What are you looking to do. Delete the entire row?

#### dirtyh2o

##### New Member
info is in a column. For example:

in column A:
1. "a"
2. "b"
3. ""
4. ""
5. "c"
6. ""
7. "d"

would like to write an array formula for column B1:B7 that changes column A to:

1. "a"
2. "b"
3. "c"
4. "d"
5. ""
6. ""
7. ""

bump?

#### erik.van.geit

##### MrExcel MVP
Hi,

next formula will only work if there is at least one number in the list (you could add a "dummy" one)

A    B
1 DATA NO EMPTIES
2 1    1
3 a    a
4 b    b
5      c
6      d
7 c    e
8
9 d
10 e

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE   FORMULA (1st cell)
B2:B7   {=IF(COUNT(\$A\$2:\$A\$14)<ROW()-ROW(A2)+1,"",INDEX(\$A\$2:\$A\$14,SMALL(IF(\$A\$2:\$A\$14<>"",ROW(\$A\$2:\$A\$14)-ROW(A\$2)+1),ROW()-ROW(A\$2)+1)))}

{=formula}:
select first cell
enter formula without {}
confirm with Control-Shift-Enter
then copy down & across

[Table-It] version 06 by Erik Van Geit``````

an intresting thread: VBA or formulasystem
http://www.mrexcel.com/board2/viewtopic.php?p=1019894

kind regards,
Erik

Replies
3
Views
33
Replies
0
Views
89
Replies
6
Views
110
Replies
4
Views
48
Replies
8
Views
68