VBA Loop problem

[MrHuge]

Hi, i have the following code

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("FilterClient")) Is Nothing Then GoTo FilterProject

        'Range("FilterProject").Clear
        'Run "InsertRow"
        
        Exit Sub
        
FilterProject:

    If Intersect(Target, Range("FilterProject")) Is Nothing Then GoTo theend

        'Range("FilterClient").Clear
        'Run "InsertRow"
        Exit Sub
        
theend:

End Sub

What i am trying to do is delete the other cells contents when something is entered into the other cell, but it gets stuck in a loop, any suggestions would be fantastic.

 

[Marcelo Branco]

Hi,

To avoid an endless loop it's necessary to disable the events.

I dont know what your macro InsertRow does, so maybe something like this (try it on a copy of your workbook)

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("FilterClient")) Is Nothing Then
        Application.EnableEvents = False
        Range("FilterProject").Clear
        Application.Run "InsertRow"
        Application.EnableEvents = True
        Exit Sub
    End If
 
    If Not Intersect(Target, Range("FilterProject")) Is Nothing Then
        Application.EnableEvents = False
        Range("FilterClient").Clear
        Application.Run "InsertRow"
        Application.EnableEvents = True
    End If
 
End Sub

M.

 

[MrHuge]

Fantastic, works perfectly, Thank you.

 

[Marcelo Branco]

Fantastic, works perfectly, Thank you.

You are welcome and tks for the feedback

M.