Clear Content Button

How To Add Clear Content Button in Excel?

Learn to add a Clear Contents button in Excel with the help of VBA code to clear the contents of a specific range. Simplify your tedious jobs and save time.

Here, we will learn how to insert a command button to clear the contents of a specific range in Excel.

Key Takeaways

  • Excel allows automation of repetitive tasks like clearing data using VBA (Visual Basic for Applications). A Clear Content Button can be created using the Developer tab and an ActiveX control button.
  • VBA code (Range("C5:H12").ClearContents)is used to define and clear a specific cell range instantly.
  • This automation improves efficiency in spreadsheets like attendance sheets, reports, and trackers.

Steps to Insert a Clear Contents Button in Excel Using VBA

5 Easy steps to insert a Clear Contents button in Excel:

  1. Go to the Developer tab.
  2. Click on Insert in the Controls tab. Under ActiveX Controls, select Button. Draw the button at your desired location. Configure the button.
  3. Right-click on the button and select View Code. The Visual Basic window will open.
  4. Copy and paste the code.
  5. Click on the Save icon and close the Visual Basic window.

Let’s understand it with the help of an example.

Example

Given below is an example of weekly attendance:

Weekly Attendance

In this data, the dates, days, and attendance section change every week. Thus, to automate this process, insert a formula for the date and weekday. To clear the attendance section markings present/absent, we will insert and configure a Clear Content Button.

Go to the Developer Tab and click Insert. Select the ActiveX control button. Draw the button beside the data and configure it.

ActiveX Control Button

To configure the button, right-click the button and select Properties. You can rename the button by changing the caption. To change the fonts, click on the three dots at the end of Fonts as shown in the figure below:

Clear Content Button

Now, Right-click on the button and select View Code. The Visual Basic window will open.

Clear Content Button

Copy and paste the following code:

Range (“C5:H12”).ClearContents

VBA Module

Click on the Save icon and close the Visual Basic window.

Important Note: C5: H12 is our range here. You can type in your desired range in the code.

Clear Content Button

That’s it. The Clear Content Button now clears the data from C5:H12 as shown in the image above.

Click the link below to download the sample file:

Sample File

Additionally, you can also download HR templates such as Salary Slip Excel Template India, Salary Slip Format UAE,  Simple Salary Sheet, and Employee Salary Sheet from our website.

Frequently Asked Questions

What is VBA in Excel used for in this case?

VBA is used to automate actions like clearing data, reducing manual effort and saving time.

Do I need coding skills to create a Clear Content Button?

Basic understanding is helpful, but you mainly need to copy and paste the provided VBA code.

Can I change the cell range in the VBA code?

Yes, you can replace C5:H12 with any range you want to clear.

What is the difference between clearing and deleting cells in VBA?

ClearContents removes only data, while deleting may shift or remove entire cells or structure.

Will the button work every time I open the Excel file?

Yes, as long as macros are enabled and the file is saved in macro-enabled format (.xlsm).

If you like this article, kindly share it on different social media platforms so that your friends and colleagues can also benefit from it. Sharing is Caring.

If you have any queries or suggestions, please share them in the comment section below. We will be more than happy to assist you.

Click to listen highlighted text!