×

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

Excel VBA UCase Function

The UCase function in VBA converts a String into upper case text. Syntax UCase (String) Parameter String (required) – This parameter represents the string that you want to convert to upper case. Return This function returns a string...

1 minute read.

Excel VBA: IF THEN Statement

VBA Excel: IF THEN StatementThis conditional statement enables you to check one condition and on the basis of that then run one or multiple statements if the condition holds. If...

1 minute read.

VBA ActiveCell Property

What is the ActiveCell Property? The active cell signifies the active selected cell in the current worksheet. The Active property acts as a reference point and is used to move the cell cursor...

5 minutes read.

VBA Option Explicit

VBA Option Explicit The Option Explicit in VBA is used to declare the variables at the top of your macro code. It is the most secure and easy option to maintain your variables....

5 minutes read.

Excel VBA Filter Function

VBA Filter Function: The Filter function in VBA returns a subset for the given string array, based on specified criteria. Syntax Filter (SourceArray, Match, [Include], [Compare]) Parameter SourceArray (required) – This parameter the array of Strings that you...

2 minutes 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.

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 Conditional Statement

Conditional Statement in VBA Excel Conditional Statements in Excel VBA are one of the most powerful and useful features in programming, this will give you to perform comparisons to decide or...

2 minutes read.

Excel VBA CSng Function

VBA CSng Function: The CSng function in VBA converts the supplied expression into a single-precision floating-point number (single data type). Syntax CSng (Expression) Parameter Expression (required) – This parameter represents the expression that you want...

1 minute read.

Excel VBA IsDate Function

VBA IsDate Function: The IsDate function in VBA returns a Boolean value indicating whether the given expression is interpreted as a VBA Date or not. Syntax IsDate (Expression) Parameter Expression (required) – This parameter...

1 minute read.

Excel VBA MonthName Function

Excel VBA MonthName Function: The MonthName function in VBA returns a string with the month name for the specified month number. Syntax MonthName (Month, [Abbreviate]) Parameter Month (required) – This parameter represents an integer between...

1 minute read.

Excel VBA CDbl Function

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

1 minute read.

Excel VBA Len Function

VBA Len Function: The Len function in VBA returns the number of characters in a supplied string or the number of bytes required to store a supplied variable. Syntax Len (Expression) Parameter Expression (required)-...

1 minute read.

VBA Global Variable

What is Global Variable? The Global Variables in VBA refers to the variables declared before the start of any macro. They are defined outside the functions and are used by all the functions or...

6 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.

VBA VBScript Regex Methods Regex

VBA VBScript Regex Methods Regex The VBA Regex supports 3 methods which are as follows: ExecuteReplaceText Execute The execute method is used to extract a match from the given based on the defined matching...

3 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 Left Function

VBA Left Function: The Left function in VBA returns a substring from the start of the specified string. Syntax Left (Str, Length) Parameter Str (required) – This parameter represents the string that you want to extract...

1 minute read.

Excel VBA INT Function

VBA INT Function: The INT function in VBA rounds the given supplied number down and returns an integer value. The positive numbers are rounded to zero, and the negative numbers are rounded away from zero. Syntax Int...

1 minute read.

Excel VBA Val Function

VBA Val Function: The Val function in VBA converts the given string into a numeric value. This function ignores spaces and continues to read the characters after space(s). It stops...

1 minute read.