COPY PASTE VALUES ONLY FROM/ON VISIBLE CELLS

[FARAZ QURESHI]

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!

 

[joel]

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]

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]

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]

Thanx again Joel!

However, the problem still remains. First of all:

1. I think instead of:
Set VisibleCells = activesheet.cells.SpecialCells(xlCellTypeVisible)

it should be:
Set VisibleCells = selection.cells.SpecialCells(xlCellTypeVisible)

so as to avoid application on each and every visible cell on the sheet
taking up a neverending exercise.

And

2. The button inserted via the CustomUIEditor on the XML ribbon to run the
code, upon being clicked, is still giving the same error as earlier, i.e.:

Wrong number of arguments or invalid property assignment

 

[joel]

If you use selection then somebody has to select the area. Excel is smart.
When cells is used is default to the used area of the spreadsheet and doesn't
go to row 65536 and column 256. The only problem is if somebody was using
10,000 rows and then clears rows 1,000 to 10,000 the used area is the largest
area used on the worksheet being 10,000 rows. I haven't had any problems with
the amount of time code runs using cells.

If you are worried then use this

with Activesheet
Set LastCell = .cells.SpecialCells(xlCellTypeLastCell)
Set MyRange = .Range(.Range("A1"),LastCell)
Set VisibleCells = MyRange.SpecialCells(xlCellTypeVisible)
end with

I not sure what is cuasing the problem with the control button. I would
delete the old button and re-Add anew button. when you add the button right
click on the button and choose View Code. I think your button is pointing to
the wrong macro.

 

[FARAZ QURESHI]

Thanx again Joel!

You really are a great help! However, just figured out the problem. Stupid
me! I inserted:

Sub PstVal2VisCls()

instead of:

Sub PstVal2VisCls(control As IRibbonControl)

Really thanx again pal!