VBA - Automatically Clear Cell Information - Event Change

Asked

Viewed 608 times

4

The script below causes when selecting the column "H11" automatically clear the column "I11".

How do I make the same script erase the I12 cell when selecting the H12 cell and so following the same logic to the 5000 line?

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$H$11" Then
      Range("I11").ClearContents

  End If

End Sub

Exemplo: lista de seleção: Exemplo: lista apos selecionar

excel vba

  • You want to select the H11 column and delete from I11 to I5000, or you want to click on the H12 row and delete i12?

  • I want to select the H12 column and delete i12

  • So what would replicate up to column 5000? [Edit] the question with more information.

  • edited. can help ?

  • only one doubt, Oce already tried autofilter?

1 answer

0


The answer is similar to the answer: Copy and paste cell when changed

However, instead of copying and pasting, one should clean the cell.

Insert Event code

Use the event Worksheet_Change, where the data shall be placed within the worksheet in which the data is located. For example, in my case it was in Planilha1:

Planilha1

Code for a cell

The code is triggered every time the spreadsheet has some change in column H, where the cell in I is cleared with .ClearContents

Option Explicit
Private Const H As Long = 8                      '<-- Coluna 8 ou H
Private Const I As Long = 9                      '<-- Coluna 9 ou I
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    Application.EnableEvents = False
    Dim coluna As Long                           '<-- Coluna selecionada
    Dim linha As Long

    coluna = Target.Column
    linha = Target.Row
    'Se ocorrer mudanças na coluna H
    If coluna = H Then
        Cells(linha, I).ClearContents
    End If

CleanExit:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Code for multiple cells

This code will perform the clean-up action next to the cell that was changed in column H. Even if changes are made in multiple cells or if the interval is a non-contiguous cell range.

Resultado

Option Explicit
Private Const H As Long = 8                      '<-- Coluna 8 ou H
Private Const I As Long = 9                      '<-- Coluna 9 ou I
Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo CleanExit
    Application.EnableEvents = False
    Dim Coluna As Long                           '<-- Coluna selecionada
    Dim linha As Long
    Dim Area
    Dim rng As Range

    Coluna = Target.Column
    'Caso mais de uma célula seja alterada
    If Target.Count > 1 And Coluna = H Then
        Set rng = Range(Target.Address)
        For Each Area In rng.Areas
            Area.Offset(, 1).ClearContents
        Next
        GoTo CleanExit
    End If
    'Se ocorrer mudanças na coluna H
    If Coluna = H And Target.Count = 1 Then
        linha = Target.Row
        Cells(linha, I).ClearContents
    End If


CleanExit:
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.