Remove values in Excel

When you are working with data inside Excel worksheet, sometimes you need to delete the particular type of data.

If you have a few cells to check, you can do it manually, but when you deal with a huge amount of data, this is not the best way to achieve this.

There is a special feature in Excel that will allow you to find a specific type of data and select it.

The following example has a list of cells that look like values, but some of them are values and the other ones are formulas.

If you want to check which of them are literal values and which are formulas you can use Ctrl + ` keyboard shortcut.

Use the keyboard shortcut again to go back to the standard view.

Go To Special

Select all the cells and navigate to Home >> Editing >> Find & Select >> Go To Special. Choose Constants and check Numbers.

Now, you can see that only values are selected.

Press the Delete key to remove all of them.

Macro to remove values

Let’s record a macro to see how the VBA code looks like. Select all the cells and click a button in the bottom-left corner.

Name the Macro remove_values. You can also press Shift + X to assign a keyboard shortcut (Ctrl + Shift + X).

Press OK.

Once again, navigate to Home >> Editing >> Find & Select >> Go To Special. Choose Constants and check Numbers. Press OK. Press the Delete button to remove values.

Stop the macro by pressing the stop button in the bottom-right corner.

Go to View >> Macros >> View Macros. Click the macro and choose Edit.

This is how our macro looks like:

Sub remove_values()
'
' remove_values Macro
'
' Keyboard Shortcut: Ctrl+Shift+X
'
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
End Sub

Besides comments, there only two lines of code:

Selection.SpecialCells(xlCellTypeConstants, 1).Select

It’s responsible for selecting values that are not the result of formulas.

The next line is responsible for removing the selected content.

Selection.ClearContents