Clear Excel Sheet using VBA

To clear the entered data in the current sheet of MS-Excel application follow the below simple steps.

1. Click on " Developer" tab in MS-Excel as shown in below image.

2. Click on "insert" and select "command Button", follow image.

3. Drag the button on the sheet and right click on it, select properties->Caption->Enter text like "Empty Sheet" 

4. After inserting button, now right click on button->"View Code", enter following code in it.

Private Sub CommandButton1_Click()
Dim resp As Integer
resp = MsgBox("Are you sure you want to clear the sheet?", _
vbYesNo + vbQuestion, "Clear Sheet")
If resp = vbYes Then
    Cells.ClearContents
Else
  End If
End Sub

5. Now enter some data in the sheet, just refer below image.

6. Now, press F5 or Run the form or Click on "Empty Sheet" button and you'll see following image with confirm dialog box.

7. After pressing "Yes" button on the message box, all the data which was entered in the sheet is removed and sheet looks new! (see below image).

Note: To save your Macros for future use, please save as "Excel Macro-Enabled Workbook" in save as type drop down menu. 

 That's all for now! Happy reading! :) 

You can also visit my other Excel Examples on Macros

Macros in Excel

A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation – show a software program the steps you follow to get something done, and the software will follow along. When used right, macros can save you hours by automating simple, repetitive tasks.

A macro is a series of commands and functions that are stored in a Microsoft Visual Basic module (module: A collection of declarations, statements, and procedures stored together as one named unit. And can be run whenever you need to perform the task. 

For example, if you often enter long text strings in cells, you can create a macro to format those cells so that the text wraps. 

Recording macros When you record a macro, Excel stores information about each step you take as you perform a series of commands. You then run the macro to repeat, or "play back," the commands. If you make a mistake when you record the macro, corrections you make are also recorded. Visual Basic (Visual Basic: A high-level, visual-programming version of Basic. Visual Basic was developed by Microsoft for building Windows-based applications.) stores each macro in a new module attached to a workbook. 

To Create a Macro, Follow simple steps.

1. Start the MS Excel Application

2. Go to "Developer" Tab Menu 

3. Click "Record Macro" Button (follow screen shot) 

4. Save the macro as following screen shot.

5. After saving, Now Start typing some content as following.

6. After typing all records as shown in above image, now again go to "Developer"Tab and click on "Stop Recording" as shown in above image as well.

7. Save the work book as "Excel Macro Enabled Work Book" under "Save as Type" for future use of this macro example. (follow below screen shot image)

8. Now, Go to Sheet2 in the same work book and add a CommandButton (see below image)

9. Now, right click on Button, then select "View Code" (follow below image)

10. This will open next window as following and type following code in it to call newly created macro (refer below image)

11. Now click on "Call Macro" button to run the macro to fill all records in new sheet from existing macro.

That's all!

Next : Macros with Forms