How to copy non-contiguous range, without hidden columns for a PPT picture paste

[blueroo123]

Everyone:

I have something that has been bugging me: I am trying to use VBA to copy a range that has hidden columns in the middle (and do not want those columns copied). I know in regular Excel (Excel 2013 in my case), one simply right-clicks on the selection and the selection is copied without the hidden rows or columns. In this particular case, I am trying to do the same, but through VBA...see below my code, which does not seem to work:

  Columns("F:J").Select
    Selection.EntireColumn.Hidden = True
   
    
    Range("C7:P50").SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    
    Columns("F:J").Select
    Selection.EntireColumn.Hidden = False
    
    MsgBox "Data is now copied and ready for the PPT"
    Application.ScreenUpdating = True
    Range("c9").Select

Any thoughts?

Cheers
Jonathan

 

[Worf]

Hi Jonathan

See if this example is useful:

Sub BlueRoo()
Dim r As Range, i%, c As Range
Columns("F:J").EntireColumn.Hidden = True
Set c = Range("C7:P50").SpecialCells(xlCellTypeVisible)
Set r = c.Areas(1)
For i = 2 To c.Areas.Count      ' a range can have one or more areas
    Set r = Union(r, c.Areas(i))
Next
r.Copy Sheets("Data").Range("a1")
Columns("F:J").EntireColumn.Hidden = False
End Sub