Sub PstVal2VisCls()
Set VisibleCells = activesheet.cells.SpecialCells(xlCellTypeVisible)
For Each CELL In VisibleCells
CELL.Copy
CELL.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next
End Sub

"FARAZ QURESHI" wrote:

I know that Joel, but unfortunately you didn't understand my question. I want
the formulas of the visible cells to be CONVERTED to values while remaining
at their place. I have even developed the following code:

Sub PstVal2VisCls()
Selection.SpecialCells(xlCellTypeVisible).Select
For Each CELL In Selection
CELL.Copy
CELL.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next
End Sub

But the problem now is that when I try to access it via a button on the
ribbon the result is a dialog box stating:

Wrong number of arguments or invalid property assignment

while being played via VBA by F5 it is working great.

"joel" wrote:

You can select the visible cells only by using the menu
Edit - goto and then pressing SPECIAL button. Once you selected the
visiable cells you can copy and use PasteSpecial with values to remove the
formulas.

"FARAZ QURESHI" wrote:

I have a list with a column containing formulas. With some rows
hidden/filtered I want to copy the visible cells and copy paste special the
values of only visible cells upon the same, leaving the formulas on the
hidden cells.

Any idea/macro/code?

Thanx!