PASTE SPECIAL VALUES

[Ripote]

Dear All
Good Afternoon !!!
I have faced many this issue many times working on excel files e.g. in a excel file i have selected any particular item using "Filter" which has 5-6 line items , now suppose i want to paste special values in all those rows altogether, i can not do so a message pop-up "You can't paste this here because the Copy area and paste area aren't the same size. Select just one cell in the paste area or an area that's the same size,and try pasting again." Request you to kindly advise if there is any way to avoid this and i can paste special values

 

[Marc L]

Hi,​

​

maybe you had the worst idea to merge some cells ? If yes just unmerging your issue may be solved …​

 

[jasonb75]

Request you to kindly advise if there is any way to avoid this and i can paste special values

Pastespecial can not be used with non contiguous ranges, which a filtered range would be. You would need to use vba to convert the formula to values, the simple procedure below will convert the formulas in the selected visible cells to values.

Sub Ripote()
Dim c As Range
For Each c In Selection.SpecialCells(xlVisible)
    c.Value = c.Value
Next c
End Sub