Note: The other languages of the website are Google-translated. Back to English
English English

How to clear specified cell contents if value of another cell changes in Excel?

Supposing you want to clear a range of specified cell contents if the value of another cell is changed, how can you do? This post will show you a method to solve this problem.

Clear specified cell contents if value of another cell changes with VBA code


Clear specified cell contents if value of another cell changes with VBA code


As below screenshot shown, when the value in cell A2 is changed, contents in cell C1:C3 will be cleared automatically. Please do as follows.

1. In the worksheet you will clear cell contents based on another cell changes, right click the sheet tab and select View Code from the context menu. See screenshot:

2. In the opening Microsoft Visual Basic for Applications window, copy and paste below VBA code into the Code window.

VBA code: Clear specified cell contents if value of another cell changes

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("C1:C3").ClearContents
    End If
End Sub

Note: In the code, B2 is the cell you will clear cell contents based on, and C1:C3 is the range you will clear contents from. Please change them as you need.

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

Then you can see contents in range C1:C3 is cleared automatically when the value in cell A2 changes as below screenshot shown.


Related articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (35)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
The code to clear a cell if another changes works great!!!! But I need it to work the other way around..... How is that code written??


Thanks for your help
This comment was minimized by the moderator on the site
Dear Scott,
What do you mean work the other way around? When manually clear contents of certain cells (C1:C3), then clear content of cell A2 automatically?
This comment was minimized by the moderator on the site
hello, this works for a fixed source cell only (A2), how do this dynamically such as source is a variable cell? i tried to write
A=activecell.row
If Not Intersect(Target, Range("A"& A)) Is Nothing Then
Range("C1:C3").ClearContents
End If

this should do the job with regard to activecell (ie selection) but is not working
thanks
This comment was minimized by the moderator on the site
Good Day,
Please try the below VBA code. Thank you for your comment.

Private Sub Worksheet_Change(ByVal Target As Range)
If (Not Intersect(Target, Rows(1)) Is Nothing) And (Target.Count = 1) Then
Range("C1:C3").ClearContents
End If
End Sub
This comment was minimized by the moderator on the site
Hi, I'm looking for a way to clear a range of cells of data when an "x" is entered in a certain cell. I used the above formula and it worked perfectly for that one row. The problem is that I need to extend it down to many rows. For example, if an "x" is entered in "D13", I need the range J:13 - v:13 to be cleared. I also need that to happen if an x is entered in "D14" as in I need the range d:14 - v:14 to be cleared. Is there a way to write that? Thanks for your help!
This comment was minimized by the moderator on the site
Hi this isnt working for mine. Nothing changes but no errors either. Any tips?
This comment was minimized by the moderator on the site
Good day,
Sorry for the inconvenience. Would you provide your Office version? Thanks for your comment.
This comment was minimized by the moderator on the site
In the case of A2, how do i reference a cell from a different worksheet?
This comment was minimized by the moderator on the site
any way of doing this without VBA?
This comment was minimized by the moderator on the site
Hi Sonia,
Didn't find any solution exccept for VBA. Thanks for your comment.
This comment was minimized by the moderator on the site
What VBA code do I use if I have a table and need multiple blanks?

My table is B3:E7. If the contents in column B is cleared then I would like to have the data in column C, D, E cleared for that row. I have the below for row 3, but would like the same in row 4,5,6 and 7.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
Range("C3:E3").ClearContents
End If
End Sub
This comment was minimized by the moderator on the site
What about one to just clear the contents of any cell on a doubleclick?
This comment was minimized by the moderator on the site
Hi, how do you repeat the code for more than one cell in the example, i.e. if i delete the contents of cells a4 & a5, I want to clear the contents of b4:z4 & b5:z5 respectively, I need to do this for a large spreadsheet with 1000 rows any advice will be much appreciated
This comment was minimized by the moderator on the site
Hi Julian,i am also looking for the same, please let me know if you got any solution for this.
This comment was minimized by the moderator on the site
Please change the numbers accordingly
<div data-tag="code">Dim i As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
For i = 2 To 10
lookrange = "A" & i
contentrange = "B" & i & ":" & "C" & i
If Not Intersect(Target, Range(lookrange)) Is Nothing Then
Range(contentrange).ClearContents
End If
Next i
End Sub
This comment was minimized by the moderator on the site
Hi sagarsrinivas0312,Thank you so much for this code. I'm already searching a week for this solution!
This comment was minimized by the moderator on the site
This is great, thanks. How I do this multiple times...so clear other cells based on a different column?
This comment was minimized by the moderator on the site
riusciresti a scrivermelo in modo che debba solo fare copia ed incolla?
non riesco veramente a farlo funzionare

grazie mille
This comment was minimized by the moderator on the site
Hi,
am trying to clear extra cells of column A and B if find blank cells in column C of sheet named "Sold" and Macro runs from Sheet named "Invoice".

I got below code from a helping site but cn't get my desired.
please help me about it. Screenshot attached what i want.

Sum Clear()

Dim g As Long
For g = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(g, "C").Value = "" Then
Cells(g, "A").ClearContents
Cells(g, "B").ClearContents

End If
Next
End Sum
This comment was minimized by the moderator on the site
This doesn't appear to work if the specified cell contents are controlled by a cell on another sheet? is there a fix for this?
Example using your cell names
If cell "A2" is (=sheet1[@[a5]] and this number changes on sheet 1 then changes the contents of A2 it does not clear contents in specified range.
This comment was minimized by the moderator on the site
Private Sub Worksheet_Activate()
If Range("S2") <> Range("A2").Value Then
Range("S2") = Range("A2").Value
Range("d2:g2").ClearContents
End If
'S2' can be substituted with any cell outside of data range. What the VBA is doing is automatically updating the data for 'S2' then the data being updated is what controls you specified range and whether or not it gets cleared.
This is the only way I have found to successfully do this when using referenced cells both in and outside of worksheet.
This comment was minimized by the moderator on the site
Not quite what i need but getting there.I have an empty cell E3.I have data in B3.When i put data into E3, if it is the same as B3 then B3 is deleted.Hope you can help me out.
This comment was minimized by the moderator on the site
Is it possible to clear specified cell contents if the trigger cell contains a specific number? Say, IF cell A1 = 1, then clear Cells A2:A4?
This comment was minimized by the moderator on the site
Bonjour tout le monde,

Besoin d'aide, j'ai besoin d'effacer le contenu d'une cellule de la colonne "I" si la cellule (de la même ligne) de la colonne "O" =0, sur environ 2000 lignes avec des titres tout le 10 lignes environ.
This comment was minimized by the moderator on the site
Hello,

Just looking for an easy way to make it so if "B2" has selected "Yes" from the data validation list, cell B3 would clear it's data... and vice-versa: If "B3" has selected "Yes" from the data validation list, cell "B2" would clear it's data.

Basically B2 or B3 can say "Yes" (from the data validation list) but never at the same time, one should clear the other.
This comment was minimized by the moderator on the site
Hi Jeff,
The following VBA code can do you a favor. Please give it a try.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20220721
If Target.Cells.Count > 1 Then Exit Sub
    If (Not Intersect(Target, Range("B2")) Is Nothing) And (Target.Value = "Yes") Then
        Range("B3").ClearContents
        Else
        If (Not Intersect(Target, Range("B3")) Is Nothing) And (Target.Value = "Yes") Then
        Range("B2").ClearContents
    End If
    End If
End Sub
This comment was minimized by the moderator on the site
Buongiorno, avrei bisogno di cancellare una serie di caselle (un rettangolo, quindi su più righe e colonne) in base al valore di un'altra cella. es: se la cella A2 è inferiore di 12, il quadrato con vertici opposto C2 : F4 venga cancellato.
Grazie mille
This comment was minimized by the moderator on the site
Hi Pietro,
Sorry I don't quite understand your question. Do you mind uploading a screenshot of your data?
This comment was minimized by the moderator on the site
Помогите с решением, VBA не знаю. Мне нужно при изменении ячейки удалить данные из другой и чтобы это дейстовало на весь столбец.
Меняю А2 удаляется из G2, меняю А3 удаляется из G3, меняю A6 удаляется из G6 и т.д.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2")) Is Nothing Then
Range("G2").ClearContents
End If
End Sub


Данный код хорош для одной ячейки, а как его размножить на все ячейки столбца?
This comment was minimized by the moderator on the site
Hi Наталья,
The following VBA code can help you solve the problem. Please give it a try.

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20221013
    Dim xRight As Range
    Dim KeyCells As Range
    Set KeyCells = Range("A:A")
    Set xRight = Target.Offset(0, 6)
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        xRight.ClearContents
    End If
End Sub
This comment was minimized by the moderator on the site
Hallo,

Zu Punkt 3.
Die Taste "Andere" Finde ich nicht auf meiner Windows Tastatur. Ich Habe Strg, Alt, Tab... allerdings die Taste Andere gibt es auf meiner Tastatur leider nicht.

Lieben Gruß Mathias
This comment was minimized by the moderator on the site
Hi Mathias,
If you can't find the corresponding key on the keyboard. You can just click the Save button in the Microsoft Visual Basic Applications window to save the code and then manually close this window.
This comment was minimized by the moderator on the site
Hello,
The code below works as advertised but, the following problems occurs:

Firstly, when resizing the targeted table, all the table data is cleared AND, all but column 1 headers are re-labelled to "Column1, Column2, etc. AND the workbook autosave itslef and kills the undo.

Secondly, when deleting any table row, I get a "Run-time error 1004 (Method Offset of object Range failed.


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("F3:F500")) Is Nothing Then
     Target.Offset(0, 1).ClearContents
    ElseIf Not Intersect(Target, Range("G3:G500")) Is Nothing Then
        Target.Offset(0, 1).ClearContents
        Target.Offset(0, 2).ClearContents
    ElseIf Not Intersect(Target, Range("H3:H500")) Is Nothing Then
        Target.Offset(0, 1).ClearContents
    End If
End Sub


Any idea of what could be wrong?

Thanks in advance!
This comment was minimized by the moderator on the site
Hola, estoy trabajando una base de datos en OFFICE ONLINE a traves de ONEDRIVE, quiero que al PONER "CANCELADO" o "NOSHOW" elimine el contenido de la fila seleccionada.
This comment was minimized by the moderator on the site
Hi Angel,
The VBA code does not work in Office Online. Sorry for the inconvenience.
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations