Excel

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! 🙂

SKB

Leave a Reply

Your email address will not be published. Required fields are marked *