Clear in VBA – Content, Comment, Outline

In the previous article, we learned to use the PaseSpecial method with the help of VBA. Today we will learn to clear cell content with the help of VBA. We will learn to remove everything, clear comments, formats, hyperlinks, notes, or Outline from a cell or range.

So let us start learning.

ClearContents Method in VBA

There is a method named as ClearContents in VBA which is used with a Range object so as to clear contents in that range. There are a number of other methods that are capable of removing only Comments, Formats, Hyperlinks, Notes, or Outlines that we have covered.

You can use the ClearContents method when you wish to clear contents from the range.

Range("<Range>").ClearContents

Clear Cell Content Upon Right Click

In this section, we will learn to automate the task of learning cell contents as soon as the user right-clicks on the cell. Normally when someone right-clicks on a cell, a shortcut menu appears and the cell content remains the same. When we use the macro in this example, the cell contents would be cleared.

Follow these steps.

• Press Alt and F11 keys to open VBE.
• Open the Code window for Sheet1 from the Project Explorer.

• Paste the following code for the Right Click event of this worksheet.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Target.ClearContents
End Sub

Target refers to the cell on which the user Right Clicks.

Now this code will automatically execute when someone right-clicks on any cell in Sheet1.

Clear Everything Including Contents From Selected Range

We can use the Clear method with a Range to clear everything including contents, formatting, comments, etc. Here we are using the Selection property to refer to the Selected Range of Cells.

You can use the following Sub procedure to clear everything from the selected range using VBA.

Sub clearEverythingFromSelection()
'code to clear everything from selected range
Selection.Clear
End Sub

Other Clear Options in VBA

We can also clear specific cell components just like we do in the PasteSpecial method. For instance, we can clear only comments, Hyperlinks, or Outline. You can use the following statements to clear any range.

Sub ClearFromA1()
'clears comments
Range("A1").ClearComments
'clears outline
Range("A1").ClearOutline
'clears Hyperlinks
Range("A1").ClearHyperlinks
End Sub

Here the Range property returns a reference to cell A1. You can use any Range here.

Clear Method for Entire Worksheet

You can use the Cells property with the Worksheet object to refer to all the cells in that worksheet. Thereafter you use the Clear Method to clear everything for the cells.

You can use the following Sub procedure to clear everything from the cells of Sheet1.

Sub ClearEverythingEntireWorksheet()
Worksheets("Sheet1").Cells.Clear
End Sub

This brings us to an end.

Thank you for reading. ❤