Array Sort ignoring blanks

[Peter Davison]

I am using a sort array spill formula using column 4 as the sort -
=SORT($AB$35:$AE$1000,4,1)
but because I have many rows in the range blank when I want to sort ascending (-1) all the blank rows are at the top.
I've tried to add the filter option but my sort just ends up in a value.

Has anyone any thoughts on what I need to add to the formula to ignore blank rows?
Your help would be very much appreciated

 

[Fluff]

How about

=SORT(FILTER($AB$35:$AE$1000,$AE$35:$AE$1000<>""),4,1)

 

[Peter Davison]

That was the formula I used but it just returns a #VALUE
Any thoughts why?

 

[Peter Davison]

Could the problem be that the sort is looking at formula cells?
The range $AB$35:$AE$1000 are all formulas returning a result?

 

[Fluff]

Are you sure that you filtered on a single column?

 

[Peter Davison]

Your perfect, I completely missed that.
It works great now.
I've been looking at it for ages and never spotted that.
Thank you so much for your help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[jpelletierfl]

Is there a way to do this if some columns are always blank and we want to ignore only where one column is blank?

 

[Fluff]

As this is a significantly different, it needs a thread of its own. Thanks