Clear contents for every change in Drop down list

[Kumar M B]

Hi,
I am not so familiar with Excel, getting to learn more now. i have attached a file with a data in it. My requirement is to clear the contents of P3 cell as soon as i make any change in P2 cell. Is there a way we can do it using excel vba?
thanks in advance!!
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[jindon]

To Sheet1 code module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("o2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Range("p2").ClearContents
    Application.EnableEvents = True
End Sub

 

[YasserKhalil]

Great Mr. Jindon But I think the reference to cells are different from the request in first post ..
O2 Would be P2 & P2 would be P3

 

[Kumar M B]

To Sheet1 code module

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("o2")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Range("p2").ClearContents
    Application.EnableEvents = True
End Sub

Thanks a lot Jindon!!! that really helped

 

[Kumar M B]

Great Mr. Jindon But I think the reference to cells are different from the request in first post ..
O2 Would be P2 & P2 would be P3

Thanks YasserKhalil for correcting the cells

 

[YasserKhalil]

You're welcome..
The real thanks to The Great Jindon that we learned from him a lot and a lot
Best Regards

 

[Kumar M B]

Hi Guys,
i have continuation to my earlier question i posted. is there a simple code to extend the criteria to many columns. in my initial request it was referring to only one column. I have a requirement to have the dropdowns to many columns and the contents should get cleared for specific column based on the dropdown selection. For example if i make change to P2, P3 & P4 should get cleared. similarly if i change Q2, Q3 & Q4 should get cleared, this goes until column v.
my second request is if i further make change in P3, P4 should get cleared.

i have uploaded my file in this request.

Thanks in advance!!!

 

[YasserKhalil]

To extend to other columns you could use Resize(1,Number of Columns) ..
May be

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    If Target.Row = 2 And Target.Column > 15 Then
        Application.EnableEvents = False
            Target.Offset(1).Resize(2, 1).ClearContents
        Application.EnableEvents = True
    ElseIf Target.Row = 3 And Target.Column > 15 Then
        Application.EnableEvents = False
            Target.Offset(1).ClearContents
        Application.EnableEvents = True
    End If
End Sub

 

[Kumar M B]

To extend to other columns you could use Resize(1,Number of Columns) ..
May be

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub

    If Target.Row = 2 And Target.Column > 15 Then
        Application.EnableEvents = False
            Target.Offset(1).Resize(2, 1).ClearContents
        Application.EnableEvents = True
    ElseIf Target.Row = 3 And Target.Column > 15 Then
        Application.EnableEvents = False
            Target.Offset(1).ClearContents
        Application.EnableEvents = True
    End If
End Sub

Thanks a lot again YasserKhalil!!! this is exactly what was required

 

[YasserKhalil]

You're welcome Kumar
Glad I can offer some help