Why is same ChangeEvent behaving differently under a different scenario

[julhs]

At moment I can’t get to bottom of it.
WHY;
if I manually insert blank cells into a “NamedRange” that my ChangeEvent calls all the associated call events, JUST the ONCE & Sub will End at command.
But if I manually Copy/Paste/Insert the EXACT same number of cells into EXACT same cells of the “NamedRange”, the same ChangeEvent is being fired TWICE before it will End Sub.

 

[6StringJazzer]

' Some un-associated ChangeEvent code here.

' More un-associated Change code here

Oh, one other thing. You might think it's unassociated, but it's better to show all code.

 

[julhs]

I do religious use

Application.ScreenUpdating = False/True
Application.EnableEvents = False/True

in all my coding, they are in my testing code, just not in the one I uploaded because it was only really a portion of the complete one.

However Engberg said; (sorry cant seem to use quote directly)
Did some more testing, it seems to have to do with what Right click > Insert copied cells does. If you record a macro and insert copied cells, you get something like this.
I can only come to the conclusion that Range.Insert does two changes to the sheet in sequence, probably first moving existing values (first Worksheet_Change triggers), and then pasting the new values (Worksheet_Change triggers again).

It got me thinking and I came up with the below as a workaround/solution to prevent the 2nd running of the "Call" events"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Lastrow As Long
      Application.ScreenUpdating = False
      Application.EnableEvents = False
If Not Intersect(Target, Range("ChemicalSuppliersList_Condensed")) Is Nothing Then
      Lastrow = Range("ChemicalSuppliersList_Condensed").Cells.Find("*", SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious).Row
    ' Sub initially goes to "Else" and the two "Call" events run
    ' Then will come back to here
If Range("A" & "Lastrow") <> "" 'This re-checks for value AFTER two "Call" events have run
      Application.ScreenUpdating = True
      Application.EnableEvents = True
  Exit Sub
Else
   Call DeletCellsAndFillDown
   Call ReFormatRange
      ' More un-associated change code runs here
      ' Code will now go back to "If Not Intersect"
      ' And because ("A" & "Lastrow") will now be <>"", the code goes to "Exit Sub"
      ' So preventing the two "Call" events from running a second time
  End If
 End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Does my explanation and logic make sense?

 

[Engberg]

I don't really know what all your code does so I don't know if it's a good workaround or not. My thinking is, do you really need to use Right click > Insert copied cells, since this seems to be what is causing the issue?

 

[julhs]

Did some more testing, it seems to have to do with what Right click > Insert copied cells does. If you record a macro and insert copied cells, you get something like this.
I can only come to the conclusion that Range.Insert does two changes to the sheet in sequence, probably first moving existing values (first Worksheet_Change triggers), and then pasting the new values (Worksheet_Change triggers again).

As a by the way, what I meant by "That was sort of my thinking" was that, Range("ChemicalSuppliersList_Condensed") was being held in memory somewhere/somehow. What you said gave me a thought and came up with what's in post #11

 

[julhs]

I fully except you saying “if it's a good workaround or not”
Ultimately I am just trying to future proof myself, if the sheet IS protected I wouldn’t even be able to insert cell/rows, but if it’s NOT then it will cause issues.
Hope that makes sense?