Am I able to attach an example?

I have used ">""", I tried this =OFFSET($A$1,0,0,COUNTIF($A$1:$A$40,">"""),1) with no success.

I am able to get the range using a completely separate type for formula but cant use this in my Input Range, the formula being:

="Shee1!"&(ADDRESS(ROW(A1),COLUMN(A1)))&(ADDRESS((ROW(A1)+COUNTIF(A1:A40,">""")),COLUMN(A1)))

Returning= Sheet!A1:A14 based on the formula it looks at having returned values for 14 of the 40 cells.