When you are working with data inside an Excel worksheet, sometimes you need to delete a 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 what 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 what our macro looks like:
1 2 3 4 5 6 7 8 9 |
Sub remove_values() ' ' remove_values Macro ' ' Keyboard Shortcut: Ctrl+Shift+X ' Selection.SpecialCells(xlCellTypeConstants, 1).Select Selection.ClearContents End Sub |
Besides comments, there are only two lines of code:
1 |
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.
1 |
Selection.ClearContents |