Change formulas to values

On this page I show you how to convert formulas to values manual and with VBA code.

Manual way 1

If we want to make values of formula cells on your worksheet you must first select the cells that you want or select all the cells on the worksheet like I show you below.

1: Click on the space above the first row header and on the left of the first column header.

selectallcells

2:
CMD + a

Note: With CMD a it is possible that you must hit CMD a three times, First CMD + a selects the current region. Pressing CMD + a the second time selects the current region and it's summary rows.
Pressing CMD + a the third time selects the entire worksheet.

Now we have select the cells we can make values of the formulas like this :
  • Use CMD + c or right click on the selection and choose Copy
  • Right click on the selection and choose Paste Special
  • Click on "Values"
  • Press Esc
  • Press CMD + Arrow-Up to select A1

Manual way 2

Not many people know about this cool tip, for example if we have formulas in H2:H4 and we want to make values of the formulas in this range.

1: First we select the range
2: We move the mouse curser to right border of the selection untill we see the cursor change to a hand

mousepointer

3:
We now Right click and hold the mouse and drag the selection to the column to the right and drag it back to the H column and release the right mouse button. You will now see a new menu and if you choose the option "Copy here as values only" the formulas will be values in H2:H4. You see that you also can copy as values to another location this way.

VBA code examples

The first macro of each example use PasteSpecial xlPasteValues and the second the value property(both do the same). Remember that you must remove the worksheet protection before you can use the macro's or do this also in the macro.

Warning: Value property problem
If there are constants in the .UsedRange that have character by character formatting, the .Value = .Value will change the formatting to match the first character in that cell.


'Change all the cells in all worksheets to values
'You can remove the sh.Select line and .Cells(1).Select line if you want.
'I add it because I not like the selection of the usedrange in every sheet after you use PasteSpecial.

Sub All_Cells_In_All_WorkSheets_1()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next sh
End Sub

Sub All_Cells_In_All_WorkSheets_2()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
With sh.UsedRange
.Value = .Value
End With
Next sh
End Sub


'Change all the cells in the ActiveSheet to values

Sub All_Cells_In_Active_WorkSheet_1()
With ActiveSheet.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End Sub

Sub All_Cells_In_Active_WorkSheet_2()
With ActiveSheet.UsedRange
.Value = .Value
End With
End Sub


'Change all the cells in the CurrentRegion to values
'Use the the Shortcut Ctrl Shift * with a cell selected in your data to see the CurrentRegion.
'It is a range with data bounded by a empty row/column.

Sub CurrentRegion_Example_1()
With Range("A1").CurrentRegion
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End Sub

Sub CurrentRegion_Example_2()
With Range("A1").CurrentRegion
.Value = .Value
End With
End Sub


'Change all the cells in a range to values

Sub Range_Example_1()
With Range("A5:D100")
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End Sub

Sub Range_Example_2()
With Range("A5:D100")
.Value = .Value
End With
End Sub


'Change all the cells in a range with one or more areas to values

Sub Range_With_One_Or_More_Areas_Example_1()
Dim smallrng As Range
For Each smallrng In Range("A1:C10,E12:G17").Areas
'If you want to run the code on a selection with one or more
'areas you can use For Each smallrng In Selection.Areas
With smallrng
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Next smallrng
End Sub

Sub Range_With_One_Or_More_Areas_Example_2()
Dim smallrng As Range
For Each smallrng In Range("A1:C10,E12:G17").Areas
'If you want to run the code on a selection with one or more
'areas you can useFor Each smallrng In Selection.Areas
With smallrng
.Value = .Value
End With
Next smallrng
End Sub


'Break only formula links to other Excel workbooks

Sub Break_Links_To_other_Excel_Workbooks()
'This example convert formulas that point to another Excel workbook to values
'It will not convert other Excel formulas to values.
'Note that BreakLink is added in Excel 2002
Dim WorkbookLinks As Variant
Dim wb As Workbook
Dim i As Long

Set wb = ActiveWorkbook

WorkbookLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
If IsArray(WorkbookLinks) Then
For i = LBound(WorkbookLinks) To UBound(WorkbookLinks)
wb.BreakLink _
Name:=WorkbookLinks(i), _
Type:=xlLinkTypeExcelLinks
Next i
Else
MsgBox "There are no Links to other workbooks"
End If
End Sub
25-March-2021
Web design by Will Woodgate