Copy paste Special values when some rows are hidden

[Nick70]

Hi,

I have a formula in cells in range (C6:C50) which returns a number.
Some of the rows in range (C6:C50) are hidden due to a filter being in place.

I would like to copy/ paste special values the formulas in this range (C6:C50) so we have a value and not a formula (either for all unhidden rows or for all rows in range (C6:C50) it makes no difference).

Problem is that because some of the rows in range are hidden (due to filter) this means that when I copy/ paste special values I get message 'You can't paste this here because the Copy area and paste area aren't the same size'.

Is there a way for me to copy/ paste special values the formulas without having to clear the filter (and unhide all hidden rows)?
The filter setting is quite complex so clearing the filter is not an option.

Thanks,
N.

 

[Akuini]

Do you mean you want to replace formulas in C6:C50 with their values?
Because the range is filtered then you need to loop through it:

Sub to_value()
Dim c As Range

For Each c In Range("C6:C50")
    c.Value = c.Value
Next

End Sub

 

[Nick70]

Great thanks!
N.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.