How to apply a button to clear specific cells in Excel?
Normally, you can hold the Ctrl key to select multiple specific cells, and then clear the cell contents as you need. If you are always need to clear these specific cells time to time, you can create a clear button to clear them with just one click. In this article, I will talk about how to create a clear all button to clear some specific cell contents.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
First, you should create a shape button, and then apply a code, at last, tie the code to the shape button. Please do as follows:
1. Click Insert > Shapes > Rectangles to select the Rectangle shape, and then drag the mouse to draw a rectangle button in anywhere of the sheet as you need, see screenshot:
2. Then input the text and format the shape button as you need, see screenshot:
3. And then you should insert the VBA code, please hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Clear cell contents from specific cells:
Sub Clearcells() 'Updateby Extendoffice 20161008 Range("A2", "A5").Clear Range("C10", "D18").Clear Range("B8", "B12").Clear End Sub
Note: In the above code: A2,A5 indicate it will clear the cells in the range A2:A5, not just the two cells individually, you can add multiple ranges, such as Range("B8", "B12").Clear script within the code to clear.
4. Then save and close the code window, and then link the code to the shape button, right click the button, and select Assign Macro, in the Assign Macro dialog box, choose the Clearcells code name from the Macro name list box, and click OK button to exit this dialog. See screenshot:
5. And now, when you click the Clear All button, the specific cells you defined are cleared at once, see screenshot:
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 8 days agoIs there a way for me to keep the data validation list drops on the cells. For example my list drop includes Yes or No and I would like to reset the cells to blank but keep my data validation in the background
To post as a guest, your comment is unpublished.· 24 days agoHi,
What code do I use if I only need to clear one single cell instead of a range of cells?
To post as a guest, your comment is unpublished.· 6 months agosimply I did same in worksheet it worked when I protect the sheet it shows error 1004, any suggestion in this case?
To post as a guest, your comment is unpublished.· 6 months agoHi, Radheshyam,
To run above code in a protect worksheet, please apply the below code: (Note: change the text "password" to the password which protect your sheet)
Dim xWS As Worksheet
Dim xPsw As String
Set xWS = ActiveSheet
xPsw = "password"
On Error Resume Next
Please try, hope it can help you!
To post as a guest, your comment is unpublished.· 9 months agoI have a workbook with 11 tabs, plus one at the beginning marked as "Start Here". I want to create one button in that tab that will clear up to 9 individual cells of it's contents, or enter a "0" in it within each of these tabs. The cells I want to clear do not necessarily reside in the same spot on each page. Is this possible and how is it done? I presume this can be done in VisualBasic, but would it be easier for a neophyte to create it using Macros?
To post as a guest, your comment is unpublished.· 11 months agoHi. What is the best way to create seperate buttons to clear the contents of each row separately? eg data capturer is happy with the inputs of every other row, but then needs to clear rows 3, row 6 and row 7. What's the most efficient way to create buttons to clear in this way, ie row only?