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