Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Remove formula in filtered range

0

Hi,

I have a sheet, contains thousand of rows and hundereds of columns. Few column have formulas. I want to paste special values from formulas in selected cell (After filter).

How i can do it. Please help.

Your help will be appriciated.

Regards,

Amit Jain

Answer
Discuss

Discussion

This doesn't seem like a good idea at all unless you are going to copy the filtered data and paste it onto a new sheet.
don (rep: 1989) Aug 22, '17 at 6:47 am
Add to Discussion

Answers

0
  • Select the range you want to replace formulas in
  • On the Home tab's Clipboard menu press 'Copy' (or press Ctl+C)
  • On the Home tab's Clipboard menu press 'Paste'
  • on the command gallery that opens, press 'Values'
  • Done.

You should be careful with your filter, however. It is better than even money that the hidden cells included in the selection will be changed along with the visible ones.

Discuss
0

Below mentioned VBA code is perfect answer of my question.

Sub CPSV_MultSelec_AutofilteredData()

    Dim cel As Range

    Dim rng As Range

    'Select range before run the macro

    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    For Each cel In rng

    cel.Value = cel.Value

    Next cel

End Sub

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login