Mar 20 2021 11:33 AM
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
Mar 20 2021 01:57 PM
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
Mar 20 2021 03:36 PM