This post will guide you how to clear cell contents and formatting at the same time in excel. How to clear cells to remove the cell contents , formats, and any attached comments. When you press delete key in your keyboard to clear one cell content, it only can be cell the cell contents without removing any cell formats or cell comments.
Clear Cell Contents and Formatting
To clear all contents and formats, you need to do the following steps:
#1 select the cells, rows, or columns that you want to clear contents or formats
#2 go to HOME tab, click Clear button under Editing group, and then click Clear All from the drop down list.
- If you only want to clear formats, and you click Clear formats.
- If you only want to clear the contents in the selected cells, just click Clear contents.
- If you only want to clear the comments that are attached to the selected cells, just click Clear comments.
#3 Lets see the result:
Clear Cell Contents and Formatting with VBA
You can also use the VBA Macro code to clear all contents and formats in your selected cells, rows or columns, just do the following steps:
#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
#2 then the “Visual Basic Editor” window will appear.
#3 click “Insert” ->”Module” to create a new module
#4 paste the below VBA code into the code window. Then clicking “Save” button.
Sub clearAll() Dim R As Range Set R = Application.InputBox("please select rows, or columns that you want to clear：", "clear contents and formats", Selection.Address, , , , , 8) If R Is Nothing Then Exit Sub Application.ScreenUpdating = False R.Clear Application.ScreenUpdating = True End Sub
#5 select cells that you want to clear, such as: A2:A3
#6 back to the current worksheet, then run the above excel macro. Click Run button.
#7 lets see the result: