|Well, what you asked for in your first post doesn't provide all of the detail that you provided in your second.|
You merely asked how to "pull all the numbers valued between 1 and 2" without any mention of ranking them or deleting blank rows.
We don't read minds, ya know! ;-)
Anyway, I can't think of any way to accomplish your goal with built in Excel functions.
I've seen some UDF's (User Defined Functions, written in VBA) that will return the MAX value between 2 values, but nothing that RANKS a range of values between 2 values. I suppose one could be written to do it, but it would be tricky.
Have you considered using an Advanced Filter with Criteria?
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.