How to remove formulas from sheet but keep their calculated values

Question

Can you please let me know how I can remove all formulas from a sheet but keep the results of calculations in excel VBA?

I have a sheet called map which has lots of calculation columns there now I would like to remove all of this formulas but still keep the result to save into a new sheet.

You don't need VBA for this :) Cells - Copy - Paste Special Values — Siddharth Rout, Dec 19 '13 at 01:24
I'd echo Siddarth. If you need to do it programmatically, record the macro for Select All > Paste Special... > Values. — Marc, Dec 19 '13 at 01:25
Hi all and thanks for reply, I am getting this sheets alot so no time to do this things manualy — Behseini, Dec 19 '13 at 01:29
@rdhs: That is a wrong way. The data from the entire sheet will get deleted. I guess you wanted to say `Sheet.UsedRange.Value = Sheet.UsedRange.Value` — Siddharth Rout, Dec 19 '13 at 01:51
Ok, this didnt work! all I want to do is removing all formulas from a entire a sheet but keep the values. How i can do this by recording a Macro? I can copy and paste some where else but this is not what i am looking for — Behseini, Dec 19 '13 at 01:51
@Siddharth, Thanks but how I can call this from a Module? I mean I have a sheet which is like Set ws = ThisWorkbook.Sheets("DTMGIS") so how I can use the Sheet.UsedRange.Value = Sheet.UsedRange.Value after that? thanks — Behseini, Dec 19 '13 at 01:55
If you don't want to use VBA, the easiest way to remove formulas from spreadsheets is to use this tool: http://goo.gl/EEuxV8 — artifex_knowledge, Mar 15 '16 at 12:20

Siddharth Rout · Accepted Answer · 2013-12-19T02:01:13.627

22

Way 1 (Courtesy @rdhs)

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("DTMGIS")

    ws.UsedRange.Value = ws.UsedRange.Value
End Sub

Way 2 Using Copy - PasteSpecial - Values

Sub Sample()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("DTMGIS")

    With ws.UsedRange
        .Copy
        .PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
End Sub

Way 3 Using SpecialCells

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("DTMGIS")

    On Error Resume Next
    Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0

    If Not rng Is Nothing Then
        rng.Value = rng.Value
    End If
End Sub

edited Dec 19 '13 at 02:01

answered Dec 19 '13 at 01:55

Siddharth Rout

147,039
17
206
250

Thanks Siddharth, just a quick question is there another way to select the sheet instead of Set ws = ThisWorkbook.Sheets("DTMGIS") I mean I want to work with this sheet but sometimes the name is a little bit diffrent based on people how sunmitting like DTM_GIS or DTM GIS. so can I select the sheet based on number or activity? – Behseini Dec 19 '13 at 02:21
Use the code name of the sheet. That ways no matter what the sheet name is, it will work. See [THIS](http://msdn.microsoft.com/en-us/library/office/aa214189%28v=office.11%29.aspx) and [THIS](http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm) – Siddharth Rout Dec 19 '13 at 02:24
It should be noted that the first method requires manual calculation mode. – Tam Le Apr 09 '20 at 11:36

How to remove formulas from sheet but keep their calculated values

1 Answers1

Linked