Copy Non-Blank Cells to Another Worksheet

My worksheet has thousands of rows but in Column A there are only a few dozen entries; most Column A cells are blank. The goal is to copy and paste the populated cells to another worksheet. What is the fastest way? I tried sorting first but that seems unbearably slow. Also, is it better to copy the cells into a variable or array and then paste them to the new worksheet or find each one and paste individually.

Thanks!

Maybe:

Please Login or Register  to view this content.

Hi John,

Thanks for the quick suggestion!

This line had an error...

Please Login or Register  to view this content.

"AutoFilter method of Range class failed"

Could this have anything to do with the fact that my worksheets are tables (ListObjects)?

Tom,
Yes, if you have a table, the filter should apply to the list objects.

Understood. But I am getting that error whether filtering was on or off before execution. What needs to be changed in the code? Also, John, I only need to copy the filtered contents of Column A, not the row. Thanks.

I'm not sure of what may be causing the error. Can you post a sample file with some data? If you just wanted the contents of Column A then this should work.

Please Login or Register  to view this content.

Hi John,

I have not bothered yet to test your code again. You asked for a sample worksheet so I have developed that instead. The actual job is a bit bigger than my original request. we are extracting data from three worksheets instead of just one. The sample shows the entire job. It also has an Info worksheet that explains in detail what is needed.

Thanks for your post. I hope you will find my sample adequate.

Thanks again for your help.

tom