Different ways to clear a range using VBA in Excel

Clear All Clear Comments Clear Contents Clear Formats Clear Hyperlinks Clear Notes Clear Outline range clear menu

To clear a certain area consists of two steps:

  1. Specify the range that needs to be cleared
  2. Select a suitable clear method

As an example, the code below clears everything from the entire worksheet specified with name 'Data' in the workbook in which this code is contained, fast by using limiting the range to UsedRange

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("Data")
Dim rng As Range: Set rng = ws.UsedRange
rng.Clear

Clear All

Clears all aspects mentioned below: content, Format, comments and more.

rng.Clear

Clear Comments

Clears all cell comments from the specified range.

rng.ClearComments

Clear Contents

Clears the data and formulas from the range. It leaves the formatting intact.

rng.ClearContents

Clear Formats

Clears the formatting of the range.

rng.ClearFormats

Clear Hyperlinks

Removes all hyperlinks from the specified range; all other cell content, such as text and formatting will be unaffected

rng.ClearHyperlinks

Clear Notes

Clears notes and sound notes from all the cells in the specified range.

rng.ClearNotes

Clear Outline

Clears the outline for the specified range.

rng.ClearOutline