How to clear contents and formatting at the same time in cells in Excel?

In Excel, pressing the Delete key only removes the contents of selected cells while leaving the formatting intact. If you want to clear both the contents and the formatting of cells at the same time, this guide provides a simple VBA method to accomplish it.

Clear contents and formatting at the same time in cells with VBA code

Clear contents and formatting at the same time in cells with VBA code

The following VBA code will help you clear both cell contents and formatting simultaneously. Please follow these steps.

1. In the worksheet you want to clear contents and formatting from cells, please press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the VBA code into the Code window.

VBA code: Clear contents and formatting at the same time in cells

Sub sbClearCellsOnlyData()
    Dim xRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox("Please select cell(s)：", "KuTools For Excel", Selection.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    xRg.Clear
    Application.ScreenUpdating = True
End Sub

3. In the popping up Kutools for Excel dialog box, please select the cells you will clear contents and formatting at the same time, and then click the OK button. See screenshot:

Then you can see contents and formatting of selected cells are cleared immediately as below screenshot shown.

Related articles:

• How to clear contents of combo box with VBA code in Excel?
• How to clear contents of named range in Excel?
• How to clear restricted values in cells in Excel?
• How to clear specified cell contents if value of another cell changes in Excel?
• How to clear specified cell contents on open and exit in Excel workbook?