How to Clear Cells in Excel VBA?

In this post, you’ll be learning Cells in Excel VBA and how you can clear cells in Excel VBA with some examples and code snippets.

How to Clear Cells in Excel VBA?

You can clear cells or cell properties with the .Clear methods.

The following clear methods are available in Excel VBA

• Everything ( .Clear)
• Comments ( .ClearComments)
• Contents ( .ClearContents)
• Formats ( .ClearFormats)
• Hyperlinks ( .ClearHyperlinks)
• Notes ( .ClearNotes)
• Outline ( .ClearOutline)

To run the VBA the code in Excel, you must first perform the following,

• Under the developer tab, click Visual Basics
• On the insert menu click the module option
• Enter the codes and run it

Clear Cells / Ranges

To clear cells/ Ranges

Code:

Range("a1").Clear

ClearContents

ClearContents, clears the contents of the cell or a range. It does not clear the formatting.

Code

Cell("a1").ClearContents

To clear the contents of an entire range of cells:

Code

Range("a1:a5").ClearContents

Clear

To clear all cell properties from a cell:

Code

Range("a2").Clear

Clear Formatting

To clear cell formatting use ClearFormats

Code

Range("a2").ClearFormats

Clear Selection

To clear the current selection

Code

Selection.Clear

Clear Entire Sheet

To clear an entire worksheet:

Code

Sheets("Sheet1").Cells.Clear