In this article, we will learn how to clear a cell’s contents, the range of cells or, the named range. We would also learn to remove the formatting of a range or just the contents of a range with the help of VBA. This applies to Active Worksheet or any specific worksheet of the Worksheets Collection.
So let us start learning.
ClearContents Method in VBA
ClearContents is a method in VBA that is used to clear or remove the values stored in our worksheet at a specific range or cell. This method empties the range. First, we use the Range property to access a range followed by the dot operator and then use the ClearContents method.
Here, the expression can be a range of cells, a single cell, or even a named range in the Active Worksheet or any specific worksheet of the Worksheets Collection. We can access a range using the Range property. Similarly, we can refer to a particular cell of the worksheet with the help of the Cells property.
Examples to Implement
In this part of the blog, we would implement some examples to prove what we just learned.
Example 1 – Clear Contents Method – Cell and Range
Let us say we have a worksheet Sheet1 having the following content.
Let us say I wish to remove the contents of cell A1. Since cell A1 is made by the intersection of the first column and the first row of the active worksheet Sheet1, we use the Cells method as follows.
Also Read: Merge and Unmerge Cells using VBA in Excel
Sub exClearCellContent() 'delete the value in cell A1 Cells(1, 1).ClearContents End Sub
When you run the Sub Procedure, you will see that it clears what is written in cell A1 but keeps the formatting of the cell.
We would use the Range Property to refer to a range in the active worksheet.
Use the following Sub Procedure to clear the contents of range A2:A12.
Sub exClearRangeContent() 'clear the contents of a range of cells A2:A12 Range("A2:A12").ClearContents End Sub
We can clear the contents of a named range or a dynamic named range in a simple way shown below.
Note that all these Sub Procedures apply on the Sheet which is currently opened at the time when you run the Sub Procedure.
This is how we clear the cell content using VBA.
Example 2 – Clear the Cell Content as Well as Formatting
We can use the Clear method of a Range or Cells property to clear the cell values as well as the formatting applied to the cells. For instance, let us say we have Sheet2 opened containing the data as follows.
Sub exClearContentFormatting() 'clear the cell content and formatting using clear method Range("A1:D9").Clear End Sub
When you run this Sub procedure, it clears the contents as well as the formatting of the range A2:D9.
Now we have learned to clear cell contents and formatting using VBA.
Example 3 – Clear Cell Formatting using VBA
In this example, we would learn how to clear the formatting of the cells while keeping the cell content.
Let us say I have the following content in Sheet3 of my workbook.
We want to delete the formatting of cells A1, B3, and Cell C2. We can use the Cells Property to refer to these cells followed by ClearFormatting method to remove the formatting of the referred cell.
Sub exClearCellFormatting() 'clear the cell formatting 'deleting formatting of cell A1 Cells(1, 1).ClearFormats 'deleting formatting of cell B3 Cells(3, 2).ClearFormats 'deleting formatting of cell C2 Cells(2, 3).ClearFormats End Sub
Run this Sub Procedure to clear the formatting.
Sometimes, we wish to apply the operation on the sheet which is not currently active while we run the code. This is when we can refer to a specific worksheet of the workbook with Worksheets Collection.
You can learn to refer to a worksheet using the Worksheets Collection from here.
This brings us to end.
Thank you for reading.