Copy and paste random cells values from a range to another sheet column with no blanks

Hi there

I'm looking to see if there is any VBA way in which I can copy the cell values from one column, where the values may have blank cells in between, to another column in a separate sheet but with no blanks. Example:

Sheet 1 Cell range A1 to A10, but only cells A1, A4, A5 and A7 have values
I want to be able to copy these to sheet 2 column, A but to start in the first cell (a1) and populate all sequential cells without blanks, in this example A1 to A4.

Further to this the values in sheet 1 are generated via formulas, and it's only the values I need to be copied, not the formulas or formatting.

The cell ranges used above are arbitrary, as ideally I need to be flexible on the choice of ranges used.

Hope that makes sense. Any help with this greatly appreciated :-)

Maybe,

Please Login or Register  to view this content.

Hi berlan. Wasn't sure if my previous reply got posted but just in case. Thank you The coding worked perfectly. I've spent hours on this and didn't manage to solve it and you wrapped it up in minutes. Genius!

You're welcome, and thanks for the rep.

There are many ways to do this. Another example for variety, which doesn't use the clip:

Please Login or Register  to view this content.