Copy rows that are NOT blank from A1:A150 to Column B

[matthew101]

I would greatly appreciate a solution to the following:
Copy rows that are not blank from A1:A150 to Column B or copy to another work sheet. As much as half of the rows can be blank in Column A.

 

[Fluff]

If col A is values rather than formulae, try

Range("A:A").SpecialCells(xlConstants).Copy Range("B1")

 

[matthew101]

Column A contains formulas.

 

[matthew101]

The results of formula are in column A and just values are needed in column B

 

[matthew101]

Is there a way to do it with formulas instead of code?

 

[Fluff]

Yes there is, but that's not my forte.
Hopefully another member will come along & help.

 

[matthew101]

Thank you

 

[mumps]

Try this formula in B1 and copy it down to A150: =IF(A1<>"",A1,""). This may not be exactly what you wanted as it copies the blank cells as well.

 

[Tetra201]

Try placing the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) anywhere in column B and drag-copying it down as needed:

=IFERROR(INDEX($A$1:$A$150,SMALL(IF($A$1:$A$150<>"",ROW($A$1:$A$150)),ROW(A1)-ROW($A$1)+1)),"")

 

[matthew101]

Hi Tetra201, it works! Thank you.