如何从表单中删除公式,但保留其计算值 [英] How to remove formulas from sheet but keep their calculated values
本文介绍了如何从表单中删除公式,但保留其计算值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张名为map的表格现在有很多计算列,我想删除所有这些公式,但仍然保留结果保存到一个新的工作表。
解决方案
路1 (由@rdhs提供)
Sub Sample()
Dim ws As Worksheet
设置ws = ThisWorkbook.Sheets(DTMGIS)
ws.UsedRange.Value = ws.UsedRange.Value
End Sub
方式2 使用Copy - PasteSpecial - 值
Sub Sample()
Dim ws As Worksheet
设置ws = ThisWorkbook.Sheets(DTMGIS)
带有ws.UsedRange
.Copy
.PasteSpecial粘贴:= xlPasteValues,_
操作:= xlNone,SkipBlanks: = False,Transpose:= False
Application.CutCopyMode = False
End with
End Sub
方式3 使用 SpecialCells
Sub Sample()
Dim ws As Worksheet
Dim rng As Range
设置ws = ThisWorkbook.Sheets(DTMGIS)
关于错误简历Next
设置rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
错误GoTo 0
如果没有rng是没有,然后
rng.Value = rng.Value
End If
End Sub
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.
解决方案
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
这篇关于如何从表单中删除公式,但保留其计算值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文