In this article, I’ll show you how you can remove formulas from a worksheet in Excel keeping the values and formatting with VBA. First, you’ll learn to remove formulas from a selected range of cells, and then delete formulas from the whole Excel worksheet keeping values and formatting with VBA.
VBA to Remove Formulas in Excel Keeping Values and Formatting: A Quick View
Sub Remove_Formulas_from_Selected_Range()
Dim Rng As Range
Set Rng = Selection
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub
⧠Explanation of the Code:
- This code creates a Macro called Remove_Formulas_from_Selected_Range.
- First, it copies the values from all the cells of a range selected by the user.
- Then it pastes the values in the respective cells keeping the formats intact.
- Thus it removes all the formulas from a selected range of cells keeping the values and formatting.
How to Remove Formulas in Excel Keeping Values and Formatting with VBA: 2 Ways
Here we’ve got a data set with the names, starting salaries, and present salaries of some employees of a company called Jupyter Group.
Also, we have the average salary, name of the employee with the highest salary, and that with the lowest salary in separate cells of the worksheet.
Here, the present salary of each employee is 20% of the starting salary.
That is, cell D4 has the formula:
=C4+(C4*20)/100D5 has the formula:
=C5+(C5*20)/100
And so on.
Cell G7 has the average salary with the formula:
=AVERAGE(D4:D13)
Cell H7 has the employee with the highest salary with the formula:
=INDEX(B4:D13,MATCH(MAX(D4:D13),D4:D13,0),1)And cell I7 has the employee with the lowest salary with the formula:
=INDEX(B4:D13,MATCH(MIN(D4:D13),D4:D13,0),1)
Today we’ll develop Macros using Visual Basic Application (VBA) that’ll remove the formulas from this worksheet keeping the values and formatting intact.
1. VBA to Remove Formulas in Excel Keeping Values and Formatting from a Selected Range
First of all, let’s develop a Macro that’ll remove formulas from a specific range of cells, not from the whole worksheet.
For example, let’s try to remove the formulas from the employee record only (B4:D13).
⧠VBA Code:
Sub Remove_Formulas_from_Selected_Range()
Dim Rng As Range
Set Rng = Selection
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧪ Note: This code creates a Macro called Remove_Formulas_from_Selected_Range.
⧠Output:Â
First, save the file as Excel Macro-Enabled Workbook first. Then select the range of cells from where you want to remove the formulas.
Here I want to remove the formulas from the employee record only (B4:D13). So I have selected the range B4:D13.
Then run the Macro called Remove_Formulas_from_Selected_Range.
You’ll get all the formulas removed from your selected range of cells, leaving behind the values and formattings.
Read More: How to Remove Formula in Excel and Keep Values
2. VBA to Remove Formulas in Excel Keeping Values and Formatting from the Whole Worksheet
In the previous method, we developed a Macro to remove the formulas from a selected range of cells.
Now, we’ll try to remove formulas from the entire worksheet.
Use the following VBA code for this purpose.
⧠VBA Code:
Sub Remove_Formulas_from_the_Whole_Worksheet()
Sheet_Name = InputBox("Enter the Name of the Worksheet to Remove Formulas: ")
Dim Rng As Range
Set Rng = Sheets(Sheet_Name).Cells
Rng.Copy
Rng.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub⧪ Note: This code creates a Macro called Remove_Formulas_from_the_Whole_Worksheet.
⧠Output:
Come back to your worksheet and run the Macro called Remove_Formulas_from_the_Whole_Worksheet.
You’ll get an Input Box asking you to enter the name of the worksheet from where you want to remove the formulas.
Here, I want to remove the formulas from Sheet2, so I’ve entered Sheet2.
Click OK.
And you’ll get the formulas removed from the whole worksheet, keeping only the values and formatting.
It removes formulas from the present salaries of the employees.
Also, it removes the formulas from the average salary, the employee with the highest salary, and the employee with the lowest salary.
Things to Remember
- Here we’ve used the xlPasteValuesAndNumberFormats option for pasting with VBA, which keeps the values and formatting intact while pasting some values in Excel.
- Along with it, there are 11 more options for pasting values in VBA, and each of them performs a specific type of pasting.
Download Practice Workbook
Conclusion
Using these methods, you can remove the formulas from a worksheet in Excel keeping the values and formatting unchanged with VBA. Do you have any questions? Feel free to ask us.
Hello Rifat,
I adapted your logic to loop through the entire active workbook. Code works great, but I need for the cells that are Pivottables to be excluded. Any ideas?
I’ll keep searching…
Thanks,
Doug
Hello DOUG,
Hope you are doing well. You can follow the stated technique below to keep the pivot tables intact in a worksheet.
Here, in a range I have used a formula to add up the sales values, besides it, I have a pivot table that I don’t want to change.
• Type the following code.
Sub Remove_Formulas_from_the_Whole_Worksheet()
Sheet_Name = InputBox(“Enter the Name of the Worksheet to Remove Formulas: “)
Dim R As Range
Set R = Application.InputBox(Title:=”Number Format Rule From Cell”, _
Prompt:=”Select the range”, Type:=8)
Worksheets(Sheet_Name).Activate
R.Copy
R.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End Sub
After running the code, an input box will appear.
• Type the name of the sheet on which you are working (here it is Sheet1) and press OK.
Then, another input box will open.
• Select the range which you want to change and press OK.
After that, the formula from our selected range will be removed.
Thanking you
Tanjima Hossain