Learn to add a Clear Content Button in Excel with the help of VBA code to Clear Contents of a specific range. Simplify your tedious jobs and save time.
Usually, we need to do many tasks repeatedly while creating spreadsheets in Excel. These include clearing cells, deleting ranges, printing ranges, etc. You can automate these tasks with the help of VBA in Excel.
Here, we will learn how to insert a command button to Clear Contents of a specific range in Excel.
Steps to Insert Clear Content Button in Excel Using VBA
5 Easy steps to insert Clear Content Button in Excel:
- Go to the Developer tab.
- Click on Insert in the Controls tab. Under ActiveX Controls, select Button. Draw the button at your desired location. Configure the button.
- Right-click on the button and select View Code. The Visual Basic window will open.
- Copy and Paste the code.
- 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:
In this data, the dates, days, and attendance section changes every week. Thus, to automate this process insert a formula for date and weekday. To clear the attendance section markings present/absent, we will insert and configure a Clear Content Button.
Go to Developer Tab and Click on Insert. Select the ActiveX control button. Draw the button beside the data and configure it.
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 three dots at the end of the Fonts as shown in the figure below:
Now, Right-click on the button and select View Code. The Visual Basic window will open.
Copy and Paste the following between the code:
Range (“C5:H12”).ClearContents
Click on the Save icon and close the Visual Basic window.
Important Note: The C5: H12 is our range here. You can type in your desired range in the code.
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:
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.
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 ActiveX control button.
- VBA code (
Range("C5:H12").ClearContents) is used to define and clear a specific cell range instantly. - The button must be linked to code via “View Code” in the Visual Basic Editor.
- This automation improves efficiency in spreadsheets like attendance sheets, reports, and trackers.
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 the same. 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.