Formulas to Values | Mac Excel Automation

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.

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

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