How do I ignore certain events in Excel VBA for App_SheetChange?

Question

I have an App_SheetChange() handler in an add-in that does some processing as modify their spreadsheets.

I've already optimized the handler to only perform the processing if the user's update happens within the range that the processing is dependent on (only one column).

But when users delete rows or insert rows, I don't need my routine to run.

How can I somehow detect that the change that resulted in the call to App_SheetChange() was simply inserting/deleting a row, or in some other way ensure that inserting/deleting rows doesn't call App_SheetChange() in the first place (which seems a bit heavy-handed)?

One potentially-important caveat: the code has to live within the add-in, I can't add macro code to individual workbooks.

Answer 1

I can't think of any way to do it.

Maybe you could use a workaround, say, to check whether UsedRange, or CurrentRegion, or some named range lost a column/row since last App_SheetChange.

Or maybe, you could set the .ID of each cell in the column to the address of that same cell, and if on an App_SheetChange the Application.Intersect(your_column, Target).ID doesn't represent the actuall address of the Application.Intersect(your_column, Target), then a deletion (insertion) has happened, and instead of running your macro you should update the .ID property of each cell in the column again.