How To Clear Specified Cell Contents On Open And Exit In Excel Workbook?

In this article we will learn how to remove specific cell contents while opening or closing an excel workbook. This activity can be done through VBA codes that can be applied individually at a time. Either on closing the file or opening the file. This feature can be useful when you have performed some calculations or analysis at a specific range in the workbook and want the same to clear as soon as you close the file or open the file next time. Let’s see how the VBA code can be applied.

Clear Specified Cell Contents on Workbook Open

Step 1: A sample data has been taken as following below.

Step 2: In this file, we will remove the content of C1:C5.

Step 3: Press Alt+F11 keys from the keyboard and the Microsoft Visual Basic for Applications window will open.

The above editor can also be opened using the Developer’s tab as shown below:

Step 4: In the Microsoft Visual Basic for Applications window, double click ThisWorkbook available in the Project panel.

Step 5: Now copy the below VBA code and enter the same in the ThisWorkbook (Code) window.

Private Sub 
Workbook_Open() /event to call the function when the workbook will be opened.
    Application.EnableEvents = False / This property is set to False to prevent 
the application from raising any of its events.
       Worksheets("Sheet1").Range("C1:C5").Value = "" / Defining the Sheet name 
as ‘Sheet1’ and range of cells as ‘C1:C5’ whose data to be cleared when opening 
the file. You may modify this as per your need.
    Application.EnableEvents = True / specifying whether we want events to take 
place when the VBA code is running or not.
End Sub / end of sub.

Step 6: Once the code is entered, press Alt+Q keys in the Keyboard to close the Microsoft Visual Basic for Applications window.

Step 7: Next, save the file in the format as, Excel Macro-Enabled Workbook.

Step 8: Now reopen the file and the output will be as following:

Clear Specified Cell Contents on Workbook Exit

Step 1: Press Alt+F11 keys from the keyboard and the Microsoft Visual Basic for Applications window will open.

The above editor can also be opened using the Developer’s tab as shown below:

Step 2: In the Microsoft Visual Basic for Applications window, double click ThisWorkbook available in the Project panel.

Step 3: Now copy the below VBA code and enter the same in the ThisWorkbook (Code) window.

Private Sub 
Workbook_BeforeClose(Cancel As Boolean) / calling an event just before closing 
the file.
    Worksheets("Sheet1").Range("C1:C5").Value = ""/ Defining the Sheet name as 
‘Sheet1’ and range of cells as ‘C1:C5’ whose data to be cleared when closing the 
file. You may modify this as per your need.
End Sub   

Step 4: Once the code is entered, press Alt+Q keys in the Keyboard to close the Microsoft Visual Basic for Applications window.

Step 5: Next, save the file in the format as, Excel Macro-Enabled Workbook.

Step 6: Now close the file and the system will remove the specified content just before closing the file with the following prompt.

Note: In the above codes, Sheet1, and C1:C5 is the worksheet name and cell range that will clear contents from. Please change them as you need.

Conclusion

Hence, this article described the two methods to clear the content of specific cells in an excel workbook. One while opening the file and another when closing the file. Be careful while using these methods as once the data is cleared it cannot be retrieved. Keep learning and keep exploring excel.