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: Convert Formula to Value Without Paste Special in Excel (5 Easy Methods)


Similar Readings


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.

Read More: How to Show Value Instead of Formula in Excel (7 Methods)


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

2 Comments
  1. 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.

      1

      • 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

      2

      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.

      3

      Then, another input box will open.
      • Select the range which you want to change and press OK.

      4

      After that, the formula from our selected range will be removed.

      5

      Thanking you
      Tanjima Hossain

Leave a reply

ExcelDemy
Logo