VBA to Remove Formulas in Excel Keeping Values and Formatting

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, then from the whole worksheet.


VBA to Remove Formulas in Excel Keeping Values and Formatting (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

VBA Code to Remove Formulas in Excel Keeping Values and Formatting

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.

Download Practice Workbook


2 Ways to Remove Formulas in Excel Keeping Values and Formatting with VBA

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.

Worksheet to Remove Formulas in Excel Keeping Values and Formatting with VBA

Here, the present salary of each employee is 20% of the starting salary.

That is, cell D4 has the formula:

=C4+(C4*20)/100

D5 has the formula:

=C5+(C5*20)/100

And so on.

Formulas to Remove Formulas in Excel Keeping Values and Formatting with VBA

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)

Formula to Remove Formulas in Excel Keeping Values and Formatting with VBA

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.

VBA Code to Remove Formulas in Excel Keeping Values and Formatting

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.

(Read this article to see how to run a Macro in detail).

Running Macro to Remove Formulas in Excel Keeping Values and Formatting with VBA

And you’ll get all the formulas removed from your selected range of cells, leaving behind the values and formattings.

Output to Remove Formulas in Excel Keeping Values and Formatting with VBA

Read More: How to Remove Formula in Excel and Keep Values (5 Ways)


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 whole 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.

Running Macro to Remove Formulas in Excel Keeping Values and Formatting with VBA

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.

Entering Sheet Name to Remove Formulas in Excel Keeping Values and Formatting with VBA

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, each of them performs a specific type of pasting.

Go to this link to know them in detail.


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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo