Note: The other languages of the website are Google-translated. Back to English

 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.

Apply a button to clear specific cell contents with VBA code


arrow blue right bubble Apply a button to clear specific cell contents with VBA code

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:

doc button clear cells 1

2. Then input the text and format the shape button as you need, see screenshot:

doc button clear cells 2

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

doc button clear cells 3

5. And now, when you click the Clear All button, the specific cells you defined are cleared at once, see screenshot:

doc button clear cells 4


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (53)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
GIVE FORMULAS FOR SAVE AND CLEAR
This comment was minimized by the moderator on the site
This also clears any formatting of the cell. How doI do this if I wantto keep formulas and formatting suchace as shading or borders
This comment was minimized by the moderator on the site
change the command from "clear" to "clearcontents" to just erase the text inputs
This comment was minimized by the moderator on the site
what about the color?
This comment was minimized by the moderator on the site
thank you!
This comment was minimized by the moderator on the site
Thank you so much for this information!!

Another tip: I added a textbox. To combine the textbox with the shape, I selected the textbox, held down SHIFT, and then selected the shape. With those both selected I right-clicked and selected GROUP, then GROUP, again.

When these are grouped, you can still change the text and other formatting.

To ungroup, just right-click your new button and select GROUP > UNGROUP.
This comment was minimized by the moderator on the site
Or, simply right-clicking the shape allows you to change formatting and text.
This comment was minimized by the moderator on the site
Thx for this code. How to clear cell contents or ideally fill it up with 0 value in a protected sheet with just few editable cells? Thx for help on ot.
This comment was minimized by the moderator on the site
Instead of the clear command, an example line from above would look like:
Range("A2:A5") = 0
This comment was minimized by the moderator on the site
can you make this work over several sheets?
This comment was minimized by the moderator on the site
This is clearing the border also, what can i do?
This comment was minimized by the moderator on the site
change clear to clearcontents then formatting will stay the same after clearing. only text will be gone not borders and colouring etc
This comment was minimized by the moderator on the site
Hi. 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?
This comment was minimized by the moderator on the site
Hi, Put a button on top of row 3,6,7. Assign the same marco as above on each button to clear that particular row only.
This comment was minimized by the moderator on the site
I 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?
This comment was minimized by the moderator on the site
i have the same question
This comment was minimized by the moderator on the site
Use this as a script template:
Sub Clearcells()
'Updateby Extendoffice 20161008
Range("b11:d22").ClearContents 'this line refers to the page with the macro button.
Range("'Eval Score Entry'!D2:AA2").ClearContents 'this line refers to a different tab and range.
End Sub


This comment was minimized by the moderator on the site
simply I did same in worksheet it worked when I protect the sheet it shows error 1004, any suggestion in this case?
This comment was minimized by the moderator on the site
Hi, 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)
Sub ClearcellsAsProtect()
Dim xWS As Worksheet
Dim xPsw As String
Set xWS = ActiveSheet
xPsw = "password"
On Error Resume Next
xWS.Unprotect Password:=xPsw
Range("A2", "A5").Clear
Range("C10", "D18").Clear
Range("B8", "B12").Clear
xWS.Protect Password:=xPsw
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi,

What code do I use if I only need to clear one single cell instead of a range of cells?
This comment was minimized by the moderator on the site
Hi, Jennifer,
To clear only one cell, you just need to change the VBA code as this: (Note: change the cell A2 to the cell you want to clear)

Sub Clearcells()
Range("A2").Clear
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is 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
This comment was minimized by the moderator on the site
you can use = clear.


for Eg.

Range("A2", "A5") = Clear
This comment was minimized by the moderator on the site
It is not working on merged cells. :(
This comment was minimized by the moderator on the site
replace .Clear by = ""
This comment was minimized by the moderator on the site
For merged cells do you mean have the formatting for example be Range("A25","B25","C25")="" ?
This comment was minimized by the moderator on the site
Try this,

For example: The merged cells are columns A and B. You want to clear rows 2 thru 5. It'd look like the below.

Sub Clearcells()
Range("A2:B2", "A5:B5").ClearContents
End Sub
This comment was minimized by the moderator on the site
How to do on Google Sheets please ?
This comment was minimized by the moderator on the site
It celar everything including the border lines and Cells format, I want to clear the Data only.
This comment was minimized by the moderator on the site
Hello, Karam,
To only clear the data and keep the cell formatting, please apply the below code:

Sub Clearcells()
Range("A2", "A5").ClearContents
Range("C10", "D18").ClearContents
Range("B8", "B12").ClearContents
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Thank you skyyang
Now it work properly
This comment was minimized by the moderator on the site
thanks you
This comment was minimized by the moderator on the site
This formatting is seeming to not work for me, keeps giving me "compile error: Expected: end of statement"
This comment was minimized by the moderator on the site
Thanks a lot skyyang. That was exactly what I was looking for, to delete the content but NOT the formatting. Thanks again.
This comment was minimized by the moderator on the site
Thanks for this. Is there a way to put more than one clear button on one worksheet? Thanks!
This comment was minimized by the moderator on the site
its not working
This comment was minimized by the moderator on the site
when the command button I press delete it also deletes the continuous cells and I already have a formula in them and I don't want to delete those
This comment was minimized by the moderator on the site
Please give marco for clear if cell content less than 4 digital numbers. Thanks
This comment was minimized by the moderator on the site
Hi! Works great for me with this code as you mentioned:

Sub Clearcells()
Range("A2", "A5").ClearContents
Range("C10", "D18").ClearContents
Range("B8", "B12").ClearContents
End Sub

However, I want to keep the number "0" or the procentage "0" for certain cells. Also, keep the previous selected colour. The reson for this is I use these cells as input cells and want to use the buttom to clear certin input cells.


Thanks in advance!
This comment was minimized by the moderator on the site
i am trying to clear certain cells across several tabs. How can i accomplish this? I have built a checklist that is about 100 questions long on several tabs, and it is hard to go back and delete the feedback from every cell.
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations