Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to clear specified cell contents if value of another cell changes in Excel?
In this article we will learn how to remove the values of specific cells if value of any linked cell gets removed or modified. This can be done using a VBA code. For example you want to clear a range of cell values if the value of a specified cell is removed or changed, then follow the below steps for the same.
Clear specified cell contents by changing the value of another cell
Step 1: Following is the sample data where on changing the value of A2 cell, the values of C1:C3 will get cleared.
Step 2: 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 3: In the Microsoft Visual Basic for Applications window, double click ThisWorkbook available in the Project panel.
Step 4: Now copy the below VBA code and enter the same in the ThisWorkbook (Code) window.
Private Sub
Worksheet_Change(ByVal Target As Range) /function call to change the value of a
range on changing the value of another cell
If Not Intersect(Target, Range("A2")) Is Nothing Then / If intersection area
does not exist, Intersection Method causes a Runtime Error, which can be avoided
using “Is Nothing” keyword, meaning “nothing” returns.
Range("C1:C3").ClearContents /Defined the range to clear the content
basis above condition.
End If /End of If condition.
End Sub /End of sub.
Step 5: Once the code is entered, press Alt+Q keys in the Keyboard to close the Microsoft Visual Basic for Applications window.
Step 6: Next, save the file in the format as, Excel Macro-Enabled Workbook.
Step 7: Now reopen the file and the output will be as following:
Here you can see content in range C1:C3 is cleared automatically as soon as the value in cell A2 changes as per the above screenshot.
Conclusion
Hence, this article described the method to clear the content of specific cells in an excel workbook on changing the value of another cell. Be careful while using this method as once the data is cleared it cannot be retrieved. Keep learning and keep exploring excel.
446 Views
