# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Return non-blank Cells from a Range

## Jonathan78

Hi all,

Is there a non-array way to return only the non-blank cells from a range?
At the moment I use below formula, but it slows down the file considerably 




```
Please Login or Register  to view this content.
```

----------


## martindwilson

filter, then copy paste
or even.
go to/special blanks and delete

----------


## Jonathan78

> filter, then copy paste
> or even.
> go to/special blanks and delete



Hello Martin,

Problem is that the data is the result of several parameters and is continually changing.
So I need a formula for this

----------


## wenshui2006

try this array formula
=if(row(1:1)>counta($D$4:$D$50),"",INDEX($D$4:$D$50,MATCH(,COUNTIF($E$2:E3;$D$4:$D$50),)))

----------


## pb71

Try this (no need to confirm with Ctrl+Shift+Enter):

Excel 2003:
=IF(ISERROR(INDEX(D:D,SMALL(INDEX(NOT(ISBLANK($D$2:$D$20))*ROW($D$2:$D$20),0),COUNTBLANK($D$2:$D$20)+ROW(D1)))),"",INDEX(D:D,SMALL(INDEX(NOT(ISBLANK($D$2:$D$20))*ROW($D$2:$D$20),0),COUNTBLANK($D$2:$D$20)+ROW(D1))))

Excel 2007:
=IFERROR(INDEX(D:D,SMALL(INDEX(NOT(ISBLANK($D$2:$D$20))*ROW($D$2:$D$20),0),COUNTBLANK($D$2:$D$20)+ROW(D1))),"")

----------


## Jonathan78

Awesome, thanks PB!

----------


## Andyprince

Kick ***!
Thanks for posting this, I was hitting plenty of dead ends trying to figure this out myself with Index-Match functions. 

Best
Andy

----------


## danygiguere23

Hi Andy. Could you please tell me what is the Index-Match formula that you came up with as I am looking for the same thing ?

Thanks

Dany

----------


## Mecha_Trueno

Deleted post.
Started new thread.

----------


## arlu1201

Mecha,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## douge1238

Attachment 279560

Hi pb71,

Thank you for the solution, but this doesn't seem to work for larger ranges with more blanks. Can you take a look at my attached file and let me know if you have a new solution?

Thanks,
Doug

----------


## arlu1201

douge1238,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do.  Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

----------


## douge1238

arlu1201,

It's not really a new question if the "solution" doesn't work...

----------


## arlu1201

Sometimes the solution you require could be completely different than the original posters solution, hence we encourage to post in a new thread to avoid confusing other users who read this thread.

----------


## sguna1982

> Kick ***!
> Thanks for posting this, I was hitting plenty of dead ends trying to figure this out myself with Index-Match functions. 
> 
> Best
> Andy



Hi Andy,

I need yor help, if we have more than one column means how can i put formual. Excel attached.

Thanks in advance,
Guna.S

----------


## strangedenial

> Hi Andy,
> 
> I need yor help, if we have more than one column means how can i put formual. Excel attached.
> 
> Thanks in advance,
> Guna.S




This is what I've been looking for...

But no one could solve the problem yet....

My brain will explode thinking about this...

Help us please !  :Smilie:

----------


## devildawg98

I realize that this is an old post, but I've just had a similar need to return all non-blank cells of a row.  However, instead of the value in the cell, how may I make use of the INDEX function to return the header row???

----------


## Tony Valko

Please start your own thread.

The solution is relatively easy!  :Smilie:

----------


## casio1970

Thank you for the file

----------


## Mr.manish

Thanks bro

----------


## Mr.manish

Thanks Bro

----------


## AliGW

Mr. Manish - what is the purpose of your posts? Whom are you thanking and why?

----------

