Forum Discussion
Problem when using PasteSpecial with SkipBlanks on filtered table
I have written a VBA script to produce a report from an Excel table. Basically, the VBA script filters the table based on user selected criteria. It then copies a range of cells (using SpecialCells(xlCellTypeVisible).Copy to ensure only the filtered data is copied). I am trying to then paste this into a new location (using PasteSpecial (xlPasteValues), SkipBlanks:=True so that if there are any blank cells in the copied data, they don't overwrite what's already there).
However, Excel is taking no notice of the "skipblanks" command, or perhaps it's just not recognising the blank cells as blank cells as it just pastes everything, blank cells included.
I've tried the exact same thing using data copied normally (eg selection.copy) from an unfiltered table and it works perfectly, so the problem seems to be related to the fact that it is copied from a filtered table.
Has anyone experienced this issue and, if so, is there a simple solution?
Thanks
Mike
2 Replies
It sounds to me as if you're involving VBA when you don't need to. If you have the most recent version of Excel installed, there's now a function called FILTER that can itself filter a table according to user-entered criteria. The report could then be generated from that filtered table.
Here's a great video that explains the function. https://www.youtube.com/watch?v=9I9DtFOVPIg
- Thanks mathetes,
Unfortunately the filter function is not adequate in this instance as my code is part of a much larger piece which automates the production of multiple reports (separate workbooks).
