If you only need to do this occasionally I suggest selecting the
column with the big gaps, including the header and in the drop down menu :
Data|Filter|Autofilter
Then in the dropdown list  which appears in the header cell choose
'(Non Blanks)'. This will hide all the rows with blanks in. Copy the
column to somewhere else in the sheet, or another sheet, in any event
avoid copying it on top of the hidden rows. Incidentally, it might be
safer to have no space between the quote marks at the end of your formula.