VBA return non blanks

Hello,

I'm working with a ton of data, and I need to arrange all of it in a specific way
I'm currently getting the data from the original sheet with formulas, but it also returns blank cells, and then putting the non blanks values in range into another column with this formula:

=IFERROR(INDEX($A$1:$A$1500,SMALL(IF(LEN($A$1:$A$1500)>0,ROW($A$1:$A$1500)),ROW(1:1))),"")

Is there a VBA code to generate a formula that could do this so it could and be a bit more efficient?

Thanks

I hope this image makes things a bit clearer...
Capture.JPG

Hi
Try to use a regular formula

Formula:

Please Login or Register  to view this content.

I hope this helps

Originally Posted by Tony Valko

The AGGREGATE function is not available in Excel 2007. See OP's profile.

I forgot to see that.

Demn. I got excited! Jajaja