CODEWITHSUNDEEP
excelvbadrop-down-menurefresh
jompi
jompi

Reputation: 360

excel clear dependent dropdownlist vba

After all, I already have the dropdownlists, the dependencies etc. and it works well, ALSO I have a vba code that, when the user change one value from the dropdownlist parent, the dependence clear their contents. BUT

That only works with that cell...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("C2")) Is Nothing Then
    Range("D2").ClearContents
End If
End Sub

Obviusly because I'm tellin vba that only C2 & D2, but What I want it's that somebody would help to figure it out how to make it the entire column, not just that specific cell, like (column - 1 ) or something... because if I copy paste those dropdownlists only works in the first one cause that is specified...

Anybody? Any ideas? please.

Here there are some pics enter image description here

enter image description here

In the pictures above, the dropdownlists work only in that specific cell, I tried what Hol told me with the function Cells(row index,columnIndex) but I need a for or something like that isn't it ? This is the first thing I'm doing in vba so I dont have a clue and I'm looking for examples and then trying, it takes too long hahaha, I've already tried instead of "C2" , Column(3) and in D2 Column(4) but I have an error in the conditional If Not Intersect(Target, Range(Column(3))) Is Nothing Then

Upvotes: 2

Views: 4369

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

As far as I understand you want your macro to works in pairs: any change in C column (as of 2nd row bottom direction) will clear cell in D column in the same row. If so the following code does the trick.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column = 3 Then
    Target.Offset(0, 1).ClearContents
End If
End Sub

Upvotes: 3

Related Questions