range and target in worksheet_change

[remko123]

Hi,

I'm using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$3" Then
Range("K3").Select
Selection.ClearContents
End If
End Sub

Additionally: based on a selection in J3 (from a dropdown list), K3 is populated (also a dropdown list). This code clears K3 (and L3 in which I'm using a vlookup) when J3 is changed. Nice!

Now for my question: I would also like to use this for J4 (clears K4), J5 (clears K5) etc. How can I do this without having to repeat this code?

Thanks for helping out this newbie!

 

[Richard Schollar]

Hi Remko123 & Welcome to the Board!

You don't actually need to select anything and to do what you want I think the following would be suitable:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng, cell
Set rng = Intersect(Target, Range("J:J").Resize(Rows.Count-2).Offset(2))
If Not rng Is Nothing Then
  Application.EnableEvents = False
  For Each cell In rng
    cell.Offset(,1).ClearContents
  Next cell
  Application.EnableEvents = True
End If
 
End Sub

 

[Bill Rockenbach]

Richard, can you clear the range without going throuh a loop?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng, cell
Set rng = Intersect(Target, Range("J:J").Resize(Rows.Count-2).Offset(2))
If Not rng Is Nothing Then
  Application.EnableEvents = False
  
    rng.ClearContents

  Application.EnableEvents = True
End If

End Sub

 

[Richard Schollar]

Looks much better without the loop to me Bill

 

[remko123]

Hi Bill, Richard,

Thank you for your prompt replies. I've tried both options and only Richard's code does the trick (works great, thanks!!!). So it seems that the loop is needed - unless I'm missing something.

Remko

 

[Bill Rockenbach]

The reason my version did not work is i left out the offset(,1)

with this code you can limit the target selection to a single cell and limit the range in column J that triggers the code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub  'to restrict target to single cell
    If Not Intersect(Target, Range("J3:J65536")) Is Nothing Then 'change range if you want to limit the target cells in column J
        Application.EnableEvents = False
        Target.Offset(, 1).ClearContents
        Application.EnableEvents = True
    End If
End Sub