Copy only data in range and not formula

[coopedup]

Hi,

I have a work sheet that has lots of data in and gets regular updates. I'm able to to copy data from 3 columns and paste into a different sheet via a simple macro but I'm having issues of selecting only the cells with data and not the "empty" cells with a formula. i.e. the empty cells with formulas will eventually get data when it is added, but until then I don't want to copy the data across.

The below is what I'm using and had hoped the xlPasteValues would have been the fix but is copying "blank" cells in exchange of the original formula. Does Copy have any options or alernatives?

Please Login or Register  to view this content.

Thanks,
Neil

[davesexcel]

You may have to loop through the range,
You can change <>0 to <>"" depending on what the formula result is.

Please Login or Register  to view this content.

[Sintek]

@ davesexcel
In this instance would one set to true again

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Does this help any?

Please Login or Register  to view this content.

[leelnich]

You didn't say if the "problem cells" comprise entire rows. If they do, try this:

Please Login or Register  to view this content.

You're still moving all the data, but with direct assignment, empty strings ("") won't transfer, so you can use SpecialCells (xlBlanks) to clean up.
Copy/PasteSpecial, on the other hand, DOES transfer empty strings, so (with respect) post #4 code won't work.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[jindon]

I'm able to to copy data from 3 columns and paste into a different sheet via a simple macro but I'm having issues of selecting only the cells with data and not the "empty" cells with a formula. i.e. the empty cells with formulas will eventually get data when it is added, but until then I don't want to copy the data across.

Try

Please Login or Register  to view this content.

[davesexcel]

@ davesexcel
In this instance would one set to true again

Please Login or Register  to view this content.

You don't have to set it to true.

[leelnich]

I'm pretty sure Excel doesn't copy non-contiguous areas in one go. IF you have to loop, read the entire range into a variant, then assign the values to the new sheet via the loop.

Please Login or Register  to view this content.

[Sintek]

@ davesexcel

You don't have to set it to true.

Thanks

[coopedup]

Thanks for all the great replys.

Some of the suggestions got me thinking. Not all of my source columns have formulas so on my original select I took one of those that I can use and then that become my search column for determing that last empty cell. This keeps the macro smaller and easier for others to manage (some of the suggestions also went way over my head but set me on a different route of thinking).

Please Login or Register  to view this content.