How to clear cells in Excel with one button

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

()

1. 05-15-2005, 04:06 PM #1

   Hello, Can anyone help me set up a button and macro to clear cells where data is entered. Lets say, I'm looking to clear Cells A1,B1,C1 and A3, B3, C3. Remember I would still need a button that would then prompt the Macro.

2. 05-15-2005, 05:07 PM #2

   1) Record yourself clearing the cells, as follows Tools Macro Record New Macro Select the cells Press delete Stop recording the macro. 2) Create button as follows View Toolbars Form From the forms bar select the button icon Drag out a rectangular button Once you have dragged it out a pop up will appear asking you to assign a macro. Select the macro you recorded in step 1) Finished. Any probs. Post back -- _______________________ Naz, London "tamato43" wrote:

   > Hello, > > Can anyone help me set up a button and macro to clear cells where data is > entered. > > Lets say, I'm looking to clear Cells A1,B1,C1 and A3, B3, C3. >

   > Remember I would still need a button that would then prompt the Macro.

   
   

3. 05-15-2005, 09:06 PM #3

   "tamato43" <>ha scritto nel messaggio
   
   > Hello, > > Can anyone help me set up a button and macro to clear cells where

   > data is entered.

   If you don't need to use it in a macro, but in your regular use of Excel, you can simply customize your toolbar to add the buttons you need. For Example, right click on a toolbar, choose Customize, from the left colum choose Edit and then from the right you can choose the Clear Conten or the CLear Format button and you can drag & drop it on your favorite toolbar... -- Hoping to be helpful... Regards Franz Verga ---------------------------------------------------------------------------------------- To reply translate from italian InVento (no capital letters) ----------------------------------------------------------------------------------------

4. 05-15-2005, 10:06 PM #4

   The newsgroup for macros is excel.programming Sorry didn't look very carefully, as I see that question was answered; however, this is a bit different. If those are the ONLY cells you want to clear, then i presume this is only for one worksheet. So you could create a shape on the worksheet itself and assign the macro to it by right-clicking on the shape. Here is a macro that is invoked by a double click event on the worksheet. Install to the worksheet by right click on sheet tab, view code. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Range("A1,B1,C1 , A3, B3, C3").ClearContents End Sub If you want a macro to assign to a shape or to a button Sub Clear_a1b1c1a3b3c3() Range("A1,B1,C1 , A3, B3, C3").ClearContents End Sub to create a shape or a toolbutton see

   http://www.mvps.org/dmcritchie/excel/toolbars.htm

   If you want something along the lines of removing constants from a selection on error resume next Selection.SpecialCells(xlConstants).ClearContents on error goto 0 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]

   My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

   
   Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

   "tamato43" <> wrote in message news:... > Hello, > > Can anyone help me set up a button and macro to clear cells where data is > entered. > > Lets say, I'm looking to clear Cells A1,B1,C1 and A3, B3, C3. >

   > Remember I would still need a button that would then prompt the Macro.

How about

Sub Clearcells() ActiveSheet.Unprotect "Pword" Range("C4:C5,D7,E3,E8,F6,G1,G5,I9").ClearContents ActiveSheet.Protect "Pword" End Sub

Glad we could help & thanks for the feedback

Pexels

You may want to clear all your work within a worksheet instead of creating a new sheet. If this is the case, you are in the right place. This article will show you how to create a button that will clear every row of a workbook excluding the first row. You'll need to keep the first row so you can keep your button handy for its next use. If you do not have the developer tab (the developer tab is needed in this tutorial) in Microsoft Excel find out how to make it visible here.

Adding the Developer Tab

Step 1: Insert a Button

First, click on the developer tab and choose the insert button. From the form control area of the drop-down, choose the button in the first row to the left. The steps are indicated by 1, 2, and 3 in the illustration below.

Inserting a Form Control Button

Created by Joshua Crowder

Next, drag the cursor somewhere in row 1 to create a button. After the outline of the button is drawn the Assign Macro box appears. Click on the record button.

Drag Cursor

Created by Joshua Crowder

Step 2: Start Recording a Macro

Next, the Record Macro window will appear. Name the macro and click OK. You can tell the macro is recording by the square stop button in the bottom left-hand corner of your screen this stop button will stop the macro at any time during recording.

Name Macro & Record

Created by Joshua Crowder

Step 3: Select Area to be Deleted

For this worksheet, everything below row 1 needs to be cleared when the button is pressed. Start by selecting row 2. Now press Ctrl + Shift + Down arrow. This will select every row below row 1.

Select Area

Created by Joshua Crowder

Step 4: Clear The Area

When all the rows are selected go to the home tab. Find the edit group and click on clear. Next click on clear all.

Created by Joshua Crowder

Step 5: Stop Recording The Macro

Press Ctrl + home to get back to the top of the workbook if necessary and click somewhere in the worksheet to deselect the selected cells. Now click on the stop button to the right of “ready” in the bottom left-hand corner. Now the macro has finished recording and the button that was drawn earlier should appear. When the button is clicked it will clear any formatting or text below row 1.

Formatting the Button

To format the button, right-click on the button and click on Format Control.

Created by Joshua Crowder

When the Format Control window appears, you will be given several options. Each tab option is listed in the table below. Make any necessary changes to format your button.

Created by Joshua Crowder

Closing the Workbook

When using macros in workbooks they must be saved under the file name.XLSM to represent a macros workbook. If the workbook is not saved under this file extension, the macro you created will not function at all. To save, select the File tab then Save As. Select the location to save in and create a file name. Next, find Excel Macro-Enabled Workbook from the “Save as type” dropdown. Now save the workbook.

Created by Joshua Crowder

Additional Resource

Please see the video below titled "Creating a Macro Button to Clear a Whole Worksheet" if you were unable to learn with the article.

Creating a Macro Button to Clear a Whole Worksheet

References

Crowder, J. (2020, January 1). How to Add the Developer Tab in Excel. Retrieved January 1, 2020, from https://youtu.be/nskuG6pK5ig.

Related Articles

To learn more about developing in Excel, I recommend taking a look at the book Microsoft Excel 2019 VBA and Macros (Business Skills) by Bill Jelen to enhance your skills.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2018 Joshua Crowder

Comments

Joshua Crowder (author) from Tampa, FL on December 02, 2019:

Thanks! I just wanted to show that everything can be deleted if you want.

Farah Kiaei on February 27, 2019:

Great idea that how much is important to clear every row of a workbook excluding the first row.