Four VBA Clear methods

In Microsoft Excel, many times, a situation arises where the user wants to clear the data or any specific range of data. What if the user automates this task with just a click of a macro button. Excel VBA facilitates this functionality wherein, by using the VBA methods, you can instruct the Excel to clear off the data with values, or formats, or with both. Let’s discuss briefly regarding those four methods:

  1. Clear method
Four VBA Clear methods

In Excel VBA, ‘Clear’ is the primary method used to clear off the data of your cells. The Range.Clear method clears all the cells that you specify in the Range object, including the cell values, formulas, and formatting.

VBA Formula

Range.Clear method

  • The Range object is usually used with VBA constructs such as the Worksheet.Cells, Worksheet.Range, Range.Offset, Range.Resize or Application.ActiveCell properties, or Range.Offset.

Example: Demonstrating a macro example that will clear all the cells’ values, including the formatting ranging from A2 to B8 defined in the Range object ‘rangeVal’ in the active worksheet.

Four VBA Clear methods

Output

Excel sheet Before applying the macro: In the below sheet, the Excel data the headers have been formatted with Bold and font color.

Four VBA Clear methods

Excel sheet after executing the macro code: Run the program with keyword shortcut F5 (in some systems where function keys have been allotted other functionalities use Fn+F5).

Four VBA Clear methods

Drawback

The main drawback of the Clear method is that along with the Excel cell values is also clears the formats (unlike border, font colors, background cell, etc.), conditional formatting. Many a time, the programmers only want to delete the cell values and instead of them want to put new values within the same sheet with the same style and formats. In that case, this method fails to achieve the objective.

To combat the above problem, Microsoft Excel VBA has introduced the clearcontents method.

  • Clearcontents Method
Four VBA Clear methods

The ClearContent method in Excel VBA is used to clear only the values and formulas present in the specified range, or cells, or even the worksheet. This method clears only the data cells in the range without disturbing the formats, unlike font styles, border, background cell, and hence, the formatting will remain untouched in the Excel Sheet. It is used with the Range object signifying the data cells where the user only wants to clear off the values or formulas, leaving the formatting intact.

VBA Formula

 Rangeobj.ClearContents method

The Range object is usually used with VBA constructs such as the Worksheet.Cells, Worksheet.Range, Range.Offset, Range.Resize or Application.ActiveCell properties, or Range.Offset.

Example: The macro example demonstrating the use of clearcontents method that will clear only the cells value ranging from A2 toB7 defined in the Range object ‘rangeVal’ in the active worksheet.

Four VBA Clear methods

Output

Excel sheet Before applying the macro: In the below sheet, the Excel data the headers have been formatted with Bold and font color.

Four VBA Clear methods

Excel sheet after executing the macro code: Run the program with keyword shortcut F5 (in some systems where function keys has been allotted other functionalities use Fn+F5). You will notice that in your excel sheet (also can refer to below image) only the data of your cells have been removed, the formatting of the sheet has been left intact.

Four VBA Clear methods

Advantages of ClearContents

  1. It is used in big corporates to prepare assignments where only the values are altered rest all the prototype of the sheet remains the same.
  2. When the user wants to create a new list but wants the headers with the same style, border, and formats.
  • ClearFormats Method
Four VBA Clear methods

The ClearFormats method in Excel VBA is used to clear only the style and formatting present in the specified range, or cells or even the worksheet. It is the vice-versa of the Clearcontents method. This method clears only the font styles, border, background cell, and the formatting in the range without disturbing the content in the Excel Sheet. It is used with the Range object signifying the data cells where the user only wants to clear off the formatting the content intact.

VBA Formula

Range.ClearFormats method

The Range object is usually used with VBA constructs such as the Worksheet.Cells, Worksheet.Range, Range.Offset, Range.Resize or Application.ActiveCell properties, or Range.Offset.

Example: The macro example demonstrating the use of the ClearFormats method that will clear only the cell formatting ranging from A2 toB7 defined in the Range object ‘rangeVal’ in the active worksheet.

Four VBA Clear methods

Output

Excel sheet Before applying the macro: In the below sheet, the Excel data the headers have been formatted with Bold and font color.

Four VBA Clear methods

Excel sheet after executing the macro code: Run the program with keyword shortcut F5 (in some systems where function keys have been allotted other functionalities use Fn+F5). You will notice that in your excel sheet (also can refer to below image), only the cell formatting has been removed, the content of the sheet has been left intact.

Four VBA Clear methods
  • Clear Cell Color method
Four VBA Clear methods

This method is used only to clear the interior color of the cell if specified by the user. Rest all the formatting, border, style, and even the content remains untouched.

VBA Formula

myRange.Interior.Color = xlColorIndexNone

  • The Range object is usually used with VBA constructs such as the Worksheet.Cells, Worksheet.Range, Range.Offset, Range.Resize or Application.ActiveCell properties, or Range.Offset.
  • The Range. Interior property returns an object denoting the cell range specified by the user.
  • The Interior.Color property sets the primary color of the cell interior to the object by the returned by the Range.Interior object.
  • The xlColorIndexNone property signifies that the color of the Interior object of the Cells is none.

Example: The macro example demonstrating the use of xlColorIndexNone method that will clear only the color of the cell ranging from A2 toB7 defined in the Range object ‘rangeVal’ in the active worksheet.

Four VBA Clear methods

Output

Excel sheet Before applying the macro: In the below sheet, the Excel data the headers have been formatted with Bold and font color.

Four VBA Clear methods

Excel sheet after executing the macro code: Run the program with keyword shortcut F5 (in some systems where function keys have been allotted other functionalities use Fn+F5). You will notice that in your excel sheet (also can refer to below image) only the cell color has been removed, the content and rest of the formatting of sheet has been left intact.

Four VBA Clear methods

Pin It on Pinterest

Share This