WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Clear Contents

VBA Clear Contents

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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

VBA Clear Contents

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.

VBA Clear contents Example 1

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

VBA Clear contents Example 1-1

VBA Clear contents Example 1-2

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

Code:

Range (“A1:C3”).Delete

VBA Clear contents Example 1-3

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

VBA Clear contents Example 1-4

If you want to delete all the cell’s data, then you can use VBA CELLS property 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course
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.

VBA Clear contents Example 1

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

Code:

Sub Clear_Example()

 Range("A1").Delete

End Sub

VBA Clear contents Example 2

I will run this code and see what happens.

VBA Clear contents Example 2-1

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

VBA Clear contents Example 2-2

Now see what happens when I run this code.

VBA Clear contents Example 2-3

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.

VBA Clear contents Example 3

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.

Example 3-1

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

Example 3-2

Select this method.

Code:

Sub Clear_Example()

  Range("A1:C3").ClearContents

End Sub

Example 3-3

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

Example 3-4

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

Example 4

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

Example 4-1

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

Recommended Articles

This has been a guide to VBA Clear Contents. Here we learn how to use the Clear, Delete, and ClearContents method in VBA to clear data in Excel along with some simple to advanced examples. Below are some useful excel articles related to VBA –

  • New Line in VBA MsgBox
  • How to Use Timer in VBA?
  • Excel VBA Break For Loop
  • Excel VBA Do Loop
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More