VBA, deleting row/column values as increment function

Question

Whenever I change a value (choose some value from data validation list) in column G, it should clear the cell in the next column H.

So when I choose value in G4, value in H4 will be deleted. When I choose value in G5, tha same would happen with H5.

I tried this, but not working:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then

For i = 4 To 154 Step 1
    If Target.Address = "$G$i" Then
        Range("Hi").Select
        Selection.ClearContents
    End If
Next i

End If

End Sub

Welcome to SO. `i` is a variable, so do not type it quoted. Quoted text are read as constant string values, not as variables. Try `Target.Address = "$G$" & i ` and `Range("H" & i).Select`. Also, you will find pretty interesting reading about [How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10717999/9199828) — Foxfire And Burns And Burns, Sep 16 '20 at 12:42

FaneDuru · Answer 1 · 2020-09-17T07:01:13.697

2

No need of iteration for such a task. Since, the cell value is changed by selecting from a drop down validation list, multiple changes are not possible. For such a case, the code simple exists:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 Then
       If Target.cells.count > 1 Then Exit Sub
       Target.Offset(0, 1).ClearContents
    End If
End Sub

edited Sep 17 '20 at 07:01

answered Sep 16 '20 at 13:31

FaneDuru

38,298
4
19
27

@Ondrej Kalus: Didn't you find some time to test the above code? If tested, didn't it behave as you need? – FaneDuru Sep 17 '20 at 07:02

score 0 · Answer 2 · answered Sep 16 '20 at 12:56

This can be done like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
    For Each oCell In Target.Cells  ' You can change many cells at once (i.e. copy-paste or select-delete)
        If oCell.Column = 7 Then ' Is it cell in G:G or any other?
            If oCell.Text <> vbNullString Then ' Has cell any value?
                oCell.Offset(0, 1).ClearContents    ' Clear cell in the next column in this row
            End If
        End If
    Next oCell
End Sub

VBA, deleting row/column values as increment function

2 Answers2