Problem when using PasteSpecial with SkipBlanks on filtered table

[Apollo1969]

I have written a VBA script to produce a report (a subset of data) from an Excel table. Basically, the VBA script filters the table based on some 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. The new location already contains some data so I am using PasteSpecial (xlPasteValues), SkipBlanks:=True so that only cells containing data are pasted and blank cells are not.

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 the 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?

TIA
Mike

[Marc L]

As SpecialCells method is useless in this case so try without it
or just activating the Macro Recorder and operating manually …

[Apollo1969]

Thanks Marc,

Unfortunately, if I don't use the SpecialCells method, Excel copies all data in the column, not just the filtered records. I've also tried recording a macro, but this doesn't achieve the required result either. I selected "skipblanks" when pasting the data, but it still pastes the blank cells over my existing data. This suggests to me that, when copying filtered data, Excel is not treating the blank cells as blanks, even though they definitely are.

[Marc L]

That's odd 'cause when operating manually with the Macro Recorder activated it wells works w/o SpecialCells
as everyone must know the Copy method works only with visible cells ! (Many samples within this forum.)
I guess you did not ever give it a try … Maybe what you see blank is not really blank …

[Apollo1969]

Thanks for your further comments Marc, yes I did try the manual macro recorder method, but it did not produce the correct results. As I mentioned, the code works perfectly if the table is not filtered, so the blank cells really are blank.

I've attached a simple example. It copies 2 columns of data from a table to another area of the spreadsheet. It then copies the third column and pastes it over the top of the previously pasted data, but should only paste non-blank cells. Works fine if the table is not filtered but doesn't work properly if the table is filtered.

The attached example uses three different methods (select special cells, select all cells and the recorded macro version). None work properly on a filtered table.

[Marc L]

From an Excel table the Copy method well works on selection only and thus on contiguous cells
so it needs to select column A, Copy, Paste then the same for column C …

To copy at once both columns SpecialCells method is usefull here
so according to your attachment a VBA demonstration as a starter :

Sub Demo1() 
         Application.ScreenUpdating = False 
         [A17].CurrentRegion.Clear 
    With [A1].CurrentRegion.Columns 
        .Item(5).NumberFormat = .Cells(2, 2).NumberFormat 
        .Item(5).Formula = "=IF(C1>0,C1,B1)" 
         Union(.Item(1), .Item(5)).SpecialCells(xlCellTypeVisible).Copy 
         [A17].PasteSpecial xlPasteValuesAndNumberFormats 
        .Item(5).Clear 
    End With 
         Application.ScreenUpdating = True 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Apollo1969]

Thanks Marc, that works (and I've learnt a couple of new tips too ).

I ended up doing something similar, in that I used an intermediate 'paste' step. I copied the filtered column, using CellTypeVisible and pasted it into some spare cells, then did a normal Copy of this and pasted it in the target range, using the SkipBlanks method and that worked perfectly. I still don't really understand why SkipBlanks doesn't work on the first paste, but guess it's just a quirk of VBA.

Thank you for all your help.

[Marc L]

It seems the normal behavior is changed when using an Excel table …

Thanks for the rep' !