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

Clear All

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


Clear Comments

Clears all cell comments from the specified range.


Clear Contents

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


Clear Formats

Clears the formatting of the range.


Clear Hyperlinks

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


Clear Notes

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


Clear Outline

Clears the outline for the specified range.