Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

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


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.


Gerelateerde artikelen:


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.
kte tabblad 201905

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!
officetab onderkant
Say something here...
symbols left.
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?

  • 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.
  • 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.
    • 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.
  • 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
  • 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
    • 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

      • 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!

    • 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.
  • 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?
  • 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
  • To post as a guest, your comment is unpublished.
    Sonia · 2 years ago
    any way of doing this without VBA?
    • 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.
  • 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?
  • 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?
    • 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.
  • 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!
  • 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
    • 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
  • 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
    • 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?