Paste on Filtered Range

[ReenenBarry]

I want to filter a list. Within the filter I want to copy a selection of cells and hard copy the formula in those cells (copy paste special values). It is possible to selected the cell and copy them but one cannot paste on a filtered range. The error message I get is "The command cannot be used on multiple selection". Im sure this can be solved with VBA somehow? Does anyone have a solution?

 

[RoryA]

For example:

Sub ConvertVisibleCellsToValues()
   Dim rngArea           As Excel.Range
   For Each rngArea In Selection.SpecialCells(xlCellTypeVisible).Areas
      rngArea.Value2 = rngArea.Value2
   Next rngArea
End Sub

Note: this will error if you have more than 8192 separate areas of cells in versions pre Excel 2010.

 

[ReenenBarry]

Thanks a million!