VBA to paste range skipping filtered rows & what is the range copy buffer called

You cannot copy a range and paste only part of it. Nor can you split the data on the clipboard to make it paste portions at a time. When you copy a range, It must be pasted into an area of the same number of contiguous rows and columns. Using the Union method or the SpecialCells method you can copy data from non contiguous ranges, however, VBA joins those ranges into one as part of the copy function and they will be pasted as a single range comprised of the numbers of columhns and rows created by the mehod used to define the copy range. Maybe this will help you to understand the mechanics of the copy and paste function.

Originally Posted by JLGWhiz

You cannot copy a range and paste only part of it. Nor can you split the data on the clipboard to make it paste portions at a time. When you copy a range, It must be pasted into an area of the same number of contiguous rows and columns. Using the Union method or the SpecialCells method you can copy data from non contiguous ranges, however, VBA joins those ranges into one as part of the copy function and they will be pasted as a single range comprised of the numbers of columhns and rows created by the mehod used to define the copy range. Maybe this will help you to understand the mechanics of the copy and paste function.

Cool, thanks for your clarifications.

I just had an idea. I could create a new worksheet.

Then paste on that worksheet.

Then transfer cell values (not format!!) one by one through iteration (or maybe by populating an array), and by and range/row property criteria (e.g. ".hidden = false"). I think I can find a method that works with this.

Then of course delete the dummy worksheet or workbook.

There's always work around! At least I always want there to be.

Gods, I swear VBA is the only thing keeping me sane at my job sometimes!