Clear specified range of cells if a specific cell value is changed

[cjms85]

Hi all,

I have a piece of code below which clears of a given row in column J if a cell in the same row in columns G, H or I is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
        If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
    
            Select Case Target.Column
                Case 7
                Target.Offset(0, 3).ClearContents
                Case 8
                Target.Offset(0, 2).ClearContents
                Case 9
                Target.Offset(0, 1).ClearContents
            End Select
        End If
exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

This seems to work pretty well for my purposes, although I realise this isn't necessarily perfect.

The problem I have, is that I also need to clear some additional cells for a given row when the value in column J is changed.

I've tried using:

Case 10
Range(Target.Offset(0,3),Target.Offset(0,5),Target.Offset(0,6),Target.Offset(0,7),Target.Offset(0,8)
,Target.Offset(0,9),Target.Offset(0,10).ClearContents

beneath the entry for Case 9, but this doesn't seem to work, as I get the argument "Wrong number of arguments or invalid property".

Can anyone advise what I can do resolve this issue?

The cells that are being cleared, are for the most part all cells with data validation lists, with indirect references to various named ranges. Might this be causing a conflict?

I can share the document if need be, but it's rather large at 31 MB.

 

[Joe4]

You are missing an ending parentheses before .ClearContents.
Also, you can combine the first three cases into one, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
        If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
              Select Case Target.Column
                Case 7, 8, 9
                    Cells(Target.Row, "J").ClearContents
                Case 10
                    Range(Target.Offset(0, 3), Target.Offset(0, 5), Target.Offset(0, 6), Target.Offset(0, 7), Target.Offset(0, 8) _
                        , Target.Offset(0, 9), Target.Offset(0, 10)).ClearContents
            End Select
        End If

exitHandler:
    Application.EnableEvents = True
    Exit Sub

End Sub

 

[cjms85]

Hi Joe4,
I've just used the code with the changed you've suggested, but I'm still getting "Compile Error: Wrong number or arguments or invalid property assignment". Any idea what might be causing it?

 

[Joe4]

Yes, you cannot specify individual cells like that. In that method, you indicate first and last cell, and it clears everything in between (sorry I missed that the first time around, though the other error I pointed out was true also).

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error Resume Next
        
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
    
        Select Case Target.Column
            Case 7, 8, 9
                Cells(Target.Row, "J").ClearContents
            Case 10
                Target.Offset(0, 3).ClearContents
                Range(Target.Offset(0, 5), Target.Offset(0, 10)).ClearContents
        End Select
     End If

exitHandler:
    Application.EnableEvents = True
    Exit Sub

End Sub