Logo
×

Four VBA Clear methods

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. 

Sub ClearCells_Example()
     'the excel macro to illustrate the working of the clear method.
     'The Range.clear method clears all the cells that you specify in the Range object
     'including the cell values, formulas and formatting.
     'declare the range object variable which will hold the cells reference to clear
     Dim rangeVal As Range 
     'identify cells to clear
     Set rangeVal = ThisWorkbook.Worksheets("Sheet1").Range("A2:B8")
     'defining the clear method
     rangeVal.Clear
 End Sub
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. 

Sub ClearContents_Example()
     'the excel macro to illustrate the working of the ClearContents method.
     'The Range.ClearContents method clears only the cell values (not the formatting including style, fonts)
     'that you specify in the Range object
     'declare the range object variable which will hold the cells reference to clear
     Dim rangeVal As Range
     'declaring the cell range to clear
     Set rangeVal = ThisWorkbook.Worksheets("Sheet1").Range("A2:B8") 
     'defining the clear method to clear off only the cell values
     rangeVal.ClearContents
 End Sub
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. 

Sub ClearFormats_Example()
     'the excel macro to illustrate the working of the ClearFormats method.
     'The Range.ClearFormats method clears only the cell formatting including style, fonts
     'that you specify in the Range object
     'declare an object variable to hold a reference to cells to clear
     Dim rangeVal As Range
     'declaring the cell range to clear
     Set rangeVal = ThisWorkbook.Worksheets("Sheet1").Range("A2:B8") 
     'defining the clear method to clear off only the cell formatting.
     rangeVal.ClearFormats
 End Sub
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.

Sub ClearColor_Example()
     'the excel macro to illustrate the working of the xlColorIndexNone method.
     'The Range.ClearFormats method clears only the cell color formatting
     'that you specify in the Range object
     'declare an object variable to hold a reference to cells to clear
     Dim rangeVal As Range
     'declaring the cell range to clear 
     Set rangeVal = ThisWorkbook.Worksheets("Sheet1").Range("A2:B8")
     'defining the clear method to clear off only the cell color.
     rangeVal.Interior.Color = xlColorIndexNone
 End Sub
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

Related Topics

VBA Dim

What is Dim? DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.)...

7 minutes read.

CodeIgniter Architecture

Here we will understand the architecture and working of the CodeIgniter application, which helps you to elaborate all steps in simple ways. As the above image represents that whenever a request comes from the...

2 minutes read.

Excel VBA DateValue Function

The DateValue function in VBA returns a VBA Date from the given String representation of a date wherein the time information is ignored. It is unable to interpret dates that include the...

1 minute read.

Excel VBA Sin Function

VBA Sin Function: The Sin function in VBA returns the sine value for a supplied angle. Syntax Sin (Number) Parameter Number (required) –This parameter represents the angle (in radians) to calculate the sine value. Return This function...

1 minute read.

Excel VBA CStr Function

VBA CStr Function: The CStr function in VBA converts an expression into a string data type. Syntax CStr (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

Excel VBA StrConv Function

The StrConv function in VBA converts a string into a specified format. Syntax StrConv (String, Conversion, [LocaleID]) Parameter String (required) – This parameter represents the string to be converted. Conversion (required) – This parameter specifies the type of conversion. It can...

1 minute read.

VBA Subscript out of Range

What is Subscript out of Range? The VBA Subscript out of Range error (which is also called as Run-Time Error 9) mostly triggers when the user selects any cell, sheet, or workbook which does...

5 minutes read.

Excel VBA DatePart Function

The DatePart function in VBA returns a part (day, month, week, etc.) for the specified date and/or time. Syntax DatePart (Interval, Date, [FirstDayOfWeek], [FirstWeekOfYear]) Parameter Interval (required) – This parameter represents a string specifying the interval to be used. It can...

2 minutes read.

Excel VBA IsArray Function

VBA IsArray Function: The IsArray function in VBA returns a Boolean, showing whether the given variable is an Array or not. Syntax IsArray (VarName) Parameter VarName (required)- This parameter represents the variable that you want...

1 minute read.

Excel VBA For Each Loop

A For Each loop executes a statement or a group of statements for each element in an array or collection. It repeats the statement/condition/code for each element in a collection. For Each Loops loop through every...

4 minutes read.

Procedures in VBA

Procedures in VBA A procedure is a block of statements or units of computer code that performs some action. It is enclosed with a declaration statement, and its primary purpose is to carry out...

3 minutes read.

VBA Pivot Table Grouping

VBA- Pivot Table Grouping For an instance, if in our pivot table, we have 11 different age groups from 20 to 30 -  but there might be a possibility that we...

2 minutes read.

VBA Regex Pattern Validation

VBA Regex Pattern Validation The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard...

6 minutes read.

Debugging in Excel VBA

Debugging in VBA: Debugging is a technique used to fix errors in programming languages. In Excel VBA, we have different ways by which you can identify the error in the...

2 minutes read.

Excel VBA : With End-with Constructs

With End-with Constructs The With-End With construct enables the user to perform multiple operations on a single object. If you are going to perform several different actions on the same object and typing the same...

4 minutes read.

VBA Cell

What is VBA cell? Cells is one of the elements (workbook, worksheet, range) in Excel VBA, which refers to cells of the Excel worksheet.  In VBA, the cell is also a property...

5 minutes read.

Excel VBA Exp Function

VBA Exp Function: The Exp function in VBA returns the value of the exponential function ex (mathematical constant ‘e’ raised to specified power) for the given value of x. Syntax Exp (Number) Parameter Number (required) –This parameter represents...

1 minute read.

For Next loop in VBA

For Next Loop The ”For Next” loop is used for a fixed number of times. It works by implementing the loop for the specified number of times. In this, the user specifies how...

3 minutes read.

Excel VBA Cos Function

VBA Cos Function: The Cos function in VBA returns the cosine value for a supplied angle. Syntax Cos (Number) Parameter Number (required) –This parameter represents the number that you want the absolute value of. Return This function...

1 minute read.

Excel VBA LBound Function

Excel VBA LBound Function: The LBound function in VBA returns the lowest subscript for the specified dimension in the given array. Syntax LBound (ArrayName, [Dimension]) Parameter ArrayName (required) – This parameter represents an array for which...

1 minute read.