How To Clear A Range Of Cells In Vba

Excel VBA Clear Contents

Clear contents is a method in VBA which is used to delete or remove the values which are stored in the cells provided to it, this method makes the cell range empty and it is used with the range property to access the specified cell range, the example to use this method is as follows range("A1:B2").ClearContents this method will clear the contents of cells from A1 to B2.

In excel, adding the data and deleting the data is a common routine task. Sometimes we delete a single cell value, sometimes many cells values, and sometime we may require to delete the entire worksheet content as well. In this article, we will show you how to use the "Clear Contents" method in Excel VBA. In VBA, we have many methods to do this like "Clear," "Delete," and "Clear Contents."

You are free to use this image on your website, templates etc, Please provide us with an attribution link Article Link to be Hyperlinked
For eg:
Source: VBA Clear Contents (wallstreetmojo.com)

What are Clear Contents in Excel VBA?

Before I tell you about Clear Contents in VBA, let me show how we can delete or clear off the data in the specific range.

For example, look at the below data.

Now, if I want to clear off from the cell A1 to C3, we need to first mention the range of cells using the VBA RANGE Range is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns. read more object.

Code:

            Range ("A1:C3")

After mentioning the range of cells by using the RANGE object, we need to select the method "Clear" to clear off the mention of the cell values.

Code:

            Range ("A1:C3").Clear

This will clear off the mentioned cell values.

Code:

            Sub            Clear_Example()    Range("A1:C3").Clear            End Sub

Apart from the clear method, we can also use the "DELETE" method as well.

Code:

            Range ("A1:C3").Delete

This will delete the mentioned cell values, just like our clear method has done.

If you want to delete all the cell's data, then you can use VBA CELLS property Cells are cells of the worksheet, and in VBA, when we refer to cells as a range property, we refer to the same cells. In VBA concepts, cells are also the same, no different from normal excel cells. read more with a worksheet name.

            Worksheets("Sheet1").Cells.Delete

            Worksheets("Sheet1").Cells.Clear

Both the above codes will delete the entire data of the worksheet "Sheet1". It will delete the cell values right from the first cell to the last cell of the worksheet.

If you want to delete the present sheet cells, then you can use the Active Sheet object.

            ActiveSheet.Cells.Delete or ActiveSheet.Cells.Clear

Difference Between Clear & Delete Methods

I know this question should have already played in your mind.

Yes, there is a difference between these two methods.

When you use the method "Delete," it will delete the cell, and the below cell will take over the position of the deleted cell.

For example, look at the below image.

Now I will use the delete method to delete the cell A1.

Code:

            Sub            Clear_Example()   Range("A1").Delete            End Sub

I will run this code and see what happens.

Look what happened here; as I told when I deleted the cell A1 it is deleted, but the cell A2 moves one cell up and occupies the deleted cell. This will lead to a data mismatch. So be careful while using the Delete method.

Now for the same data, I will clear the method.

Code:

            Sub            Clear_Example()   Range("A1").Clear            End Sub

Now see what happens when I run this code.

This code has just vacated the cell A1 without altering other cells. This looks proper method to delete only the part of the cells of the entire data range.

Use VBA Clear Contents Method to Retain Formatting of Cells

If you have observed the previous two methods, those two methods not only deleted or cleared off the cells provided. It also deleted the formatting of the cells we have provided.

In order to retain the formatting of the cells, we need not use neither "Delete" nor "Clear," but we need to use the VBA "Clear Contents" method.

The moment you enter the range of cells by using a RANGE object, it will show all the properties and methods associated with it.

We can access "Delete," we can access "Clear," and we can also "ClearContents" methods.

Select this method.

Code:

            Sub            Clear_Example()    Range("A1:C3").ClearContents            End Sub

Now, this will clear content from A1 to C3 cell, but we will have all the existing formatting as it is.

As you can see in the above picture, we have cell color in VBA, borders, and every formatting associated with those mentioned cells.

Similarly, we can clear the contents of other sheets as well.

            Worksheets("Sheet1").Range("A1:D10").ClearContents

This will clear the contents from the cells A1 to D10 in the sheet "Sheet1".

Similarly, we can delete the other open workbook cells as well.

            Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1:D10").ClearContents

Loop Through all the Worksheets and Clear Contents of Specific Range

Assume you have many sheets in your workbook, and you want to delete the range of cells from A1 to C15 in all the sheets we need to use For Each Loop in VBA VBA For Each Loop helps the user to inspect and analyze the groups of objects or values individually. It even facilitates performing the specific activity for every object or value by passing a statement or group of statements in this reference. read more to do this.

The below code will do the job.

Code:

            Sub            Clear_All()            Dim            Ws            As            Worksheet            For Each            Ws            In            ActiveWorkbook.Worksheets Ws.Range("A1:C15").ClearContents            Next            Ws            End Sub

Note: You can change the range of cells as per your wish.

Just in case if you want to clear off the entire worksheet data, then you need to use the below code.

Code:

            Sub            Clear_All()                          Dim            Ws            As            Worksheet                          For Each            Ws            In            ActiveWorkbook.Worksheets    Ws.Cells.ClearContents                          Next            Ws            End Sub

You can download this VBA Clear Contents Excel template here – VBA Clear Contents Template.