Clearing Data From Worksheet
Sponsor
Excel Vba Question:
I want to know whether its possible to press a cell or button on the worksheet to clear all entered data in the entire worksheet?
I have a worksheet in workbook that users enter several data in different pages and then print a report. The next user will have to clear all entries and then enter their own data before printing the next report. At this time, we close the program and reopen it which is a bit of pain, just wondering if thee is a VBA code to activate create a key (for example “Reset all Forms” ) on the worksheet to press and clear all data.
Excel Vba Answer:
There are two alternatives to solve your needs (excel vba to clearing data from worksheet):
1. Clear all entries on worksheet when the workbook opens
2. Clear all entries on worksheet when a button is clicked.
Let’s first create a Clear procedure, say ClearAllEntires
- go in the vba editor: from excel press ALT+F11 or menu Tolls>Macros>Visual
Basic Editor. There add a new code module (menu Insert > Module)
- in this new module enter and modify the following sub:
Public Sub ClearAllEntires()
‘here code to clear whatever needs to be cleared.
‘eg: clear sheet1 A2:C100
Thisworkbook.Worksheets(”Sheet1″).Range(”A2:C100″).ClearContents
‘other clearings on a row each
End Sub
Then, we need to excel macro function from a button
- Make the Forms toolbar is visible: menu View>Toolbars>Forms
-from the Forms toolbar, drag a button on the sheet
- the ‘Assign Macro’ dialog should pop up right away, if not, right-click
the new button and choose ‘Assign Macro’ from the pop up menu.
Assign the macro by selecting ‘ClearAllEntires’ from the list.
Now when the button is clicked the ClearAllEntires macro is run ie clearing
entry ranges. Note: you can have multiple buttons calling the same
ClearAllEntires sub.
Now, if we call the macro function when the book opens to clear any data saved with the book, the excel vba code will be:
- in the ThisWorkbook code module (from the Project Explorer window in the
vba editor, double-click it to open the module), use the Workbook_Open sub
Private Sub Workbook_Open()
ClearAllEntires
End Sub
Rate This Tips:
(No Ratings Yet)
Loading ...Incoming excel search terms
macro to clear worksheet,excel vba clear worksheet,excel macro clear worksheet,,delete all data in sheet vba,excel clear button,clear the spreadsheet vba,excel vba clear sheet,excel vba delete all data on a sheet,using vba to clear data in an excel sheet,vba write data to excel worksheet,clear all data in excel,clear data in the worksheet wiht task,clear data in worksheet,clear excel range vba,clear excel sheet vba,clear excel spreadsheet macro when opening,clear past data vba code,clear sheet macro,clear worksheet excel vba,clearing cells with vba button in excel,delete all data in worksheet vba,delete all data vba worksheet,excel clear worksheet,excel vba clea worksheet,excel vba clear a worksheet,excel vba clear all data from worksheet,excel vba delete all data in a worksheet,how to clear a worksheet in vba,how to clear data on the worksheet using vba a code,macro clear worksheet,macro to clear cells in multiple excel worksheets,mss,quick way to clear excel sheet,remove all data in a worksheet vba,vba clear data in worksheet,vba clear worksheet,vba code to clear a worksheet,vba code to clear part of a worksheet,vba excel button clear,vba excel clear data,vba excel clear worksheet,vba sub clear button,activate a worksheet from a button in vba,activating an excell worksheet saved in different workbook,add clear button to excel,button clear all data excel protected worksheet,button for clearing a worksheet,button to clear cells on multi sheets in excel,button to clear contents of sheet excel vba
Related Excel Tips
Comments
Have another excel answer or questions for this problem ?
Feel free to post it here..
Best Excel Tips
- Calling Macro Visual Basic Procedures of a Different Project
(5.00 out of 5) - How to Remove Excel Vba Macro Button (5.00 out of 5)
- Delete Worksheet (5.00 out of 5)
- Copy from Worksheet (5.00 out of 5)
- How to Calculate 6 Working Days Between Two Dates (5.00 out of 5)
- Remove Hyperlinks in Excel (5.00 out of 5)
- Unprotect Worksheet Password (5.00 out of 5)
- Calling Macro Visual Basic Procedures of a Different Project
