Hoe de gespecificeerde celinhoud wissen als de waarde van een andere cel in Excel verandert?

Stel dat u een reeks gespecificeerde celinhoud wilt wissen als de waarde van een andere cel wordt gewijzigd, hoe kunt u dat dan doen? Dit bericht laat je een methode zien om dit probleem op te lossen.

Wis de gespecificeerde celinhoud als de waarde van een andere cel verandert met VBA-code

Wis de gespecificeerde celinhoud als de waarde van een andere cel verandert met VBA-code

Verbazingwekkend! Efficiënte tabbladen gebruiken in Excel zoals Chrome, Firefox en Safari!

Bespaar 50% van uw tijd en verminder elke dag duizenden muisklikken!

Zoals onderstaand screenshot laat zien, wordt de inhoud in cel C2: C1 automatisch gewist wanneer de waarde in cel A3 wordt gewijzigd. Ga als volgt te werk.

1. In het werkblad wist u de celinhoud op basis van andere celwijzigingen, klik met de rechtermuisknop op de bladtab en selecteer Bekijk code vanuit het contextmenu. Zie screenshot:

2. In de opening Microsoft Visual Basic voor toepassingen venster, kopieer en plak onder VBA-code in het codevenster.

VBA-code: wis de gespecificeerde celinhoud als de waarde van een andere cel verandert

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

Opmerking:: In de code is B2 de cel waarvan u de celinhoud wilt wissen, en C1: C3 is het bereik waaruit u de inhoud wilt wissen. Wijzig ze indien nodig.

3. druk de anders + Q toetsen om de Microsoft Visual Basic voor toepassingen venster.

Vervolgens kunt u de inhoud in bereik C1 zien: C3 wordt automatisch gewist wanneer de waarde in cel A2 verandert, zoals onderstaand screenshot.

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.

Lees verder... Gratis download... Aankoop...

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!

Lees verder... Gratis download... Aankoop...

21 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Simon Crawley · 7 months ago
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?
- Reply
To post as a guest, your comment is unpublished.
Mattey · 1 years ago
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.
- Reply
To post as a guest, your comment is unpublished.
Mike · 1 years ago
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.
- Reply
- To post as a guest, your comment is unpublished.
  James314 · 11 months ago
  
  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.
  
  Reply
To post as a guest, your comment is unpublished.
LUCKY1987 · 1 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Julian · 1 years ago
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
- Reply
- To post as a guest, your comment is unpublished.
  sagarsrinivas0312 · 7 months ago
  
  Please change the numbers accordingly
  
  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
  
  Reply
  
  To post as a guest, your comment is unpublished.
  LoreB · 18 days ago
  
  Hi sagarsrinivas0312,
  Thank you so much for this code. I'm already searching a week for this solution!
  
  Reply
- To post as a guest, your comment is unpublished.
  Sameer · 1 years ago
  
  Hi Julian,
  i am also looking for the same, please let me know if you got any solution for this.
  
  Reply
To post as a guest, your comment is unpublished.
stephen · 2 years ago
What about one to just clear the contents of any cell on a doubleclick?
- Reply
To post as a guest, your comment is unpublished.
Matt · 2 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Sonia · 2 years ago
any way of doing this without VBA?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Hi Sonia,
  Didn't find any solution exccept for VBA. Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
GQ · 2 years ago
In the case of A2, how do i reference a cell from a different worksheet?
- Reply
To post as a guest, your comment is unpublished.
Mac · 3 years ago
Hi this isnt working for mine. Nothing changes but no errors either. Any tips?
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Good day,
  Sorry for the inconvenience. Would you provide your Office version? Thanks for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Jason · 3 years ago
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!
- Reply
To post as a guest, your comment is unpublished.
m849925x@gmail.com · 3 years ago
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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
  
  Reply
To post as a guest, your comment is unpublished.
Scott · 3 years ago
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
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  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?
  
  Reply

Log in op uw account

Wachtwoord Resetten