How to Clear Cell Contents and Formatting

,

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.

1. 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

clear cell contents1

#2 go to HOME tab, click Clear button under Editing group, and then click Clear All from the drop down list.

clear cell contents2

Note:

  • 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 contents3

2. 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.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

clear cell contents4
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

clear cell contents6
#6 back to the current worksheet, then run the above excel macro. Click Run button.

clear cell contents5

#7 lets see the result:

clear cell contents3

3. video: Clear Cell Contents and Formatting

This excel video tutorial will show you how to clear cell contents and formatting in Excel. We’ll guide you through both a manual method using Excel’s interface and an automated approach using VBA Macro, allowing you to efficiently remove data, formats, and comments from your cells.

Leave a Reply