If you have an active filter and/or some cells or columns hidden when you make a selection, Excel in fact makes automagically selection of only visible cells. The problem is that you cannot paste such non-contiguous selection to this same, non-contiguous cells, replacing e.g. formulas with values. A simple way how to achieve this effect is using a VBA loop. Just make any selection of cells and run a macro that will loop through each cell individually and paste the cell as a value. Computationally very inefficient and slow, but it works and for hundreds to small thousands of cells it is a viable option. For dozens of thousands to millions of cells run it overnight ;-)
Sub SelectionToValues()
'Converts all selected cells to values, removing formulas etc.
'Works for filtered tables and/or hidden cells/columns where the selection is non-contiguous
'and so regular Pasting copied cells as values does not work due to multiple selections.
'This method basically does brute force going through each cell individually so it is slow as hell
'and not suitable for many thousands of cells.
Dim cl As Range
Dim rng As Range
Set rng = Selection
For Each cl In rng
cl.Value2 = cl.Value2
Next cl
End Sub
