0

I have a simple macro that I want to run just one time anytime a range is changed. The problem is the code seems to run for every cell that changes in the range rather than just when anything in the range changes.

In other words, when I drop a new set of values into range("A1:A10") with ten different values, I want the macro to only run once rather than ten times.

I will post code formatted properly later but only have mobile available right now, but it is your typical:

Set keycells=range(a1:a10) 

If not application.intersect(keyCells, range(target.address)) is nothing then

Call simplemacro

End if
Ben Rhys-Lewis
  • 3,118
  • 8
  • 34
  • 45
JSS
  • 203
  • 1
  • 2
  • 11
  • How exactly are you dropping these new values into the range? – Rory Apr 04 '16 at 15:07
  • Using a copy paste into the range. I am using a dynamic range to resize based on the pasted data but left it simpler here to see if there's just a snippet of code i can add to make it happen once – JSS Apr 04 '16 at 15:18
  • As far as I know it does only occur once. My guess is that something in `simplemacro` makes a change that calls it again. Might I suggest using the following before and after your code to avoid this: `Application.EnableEvents = FALSE` and `Application.EnableEvents = TRUE`. – Demetri Apr 04 '16 at 15:24
  • Then as @Demetri said, it will only be triggered once by the paste. – Rory Apr 04 '16 at 15:25
  • See [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) on how to work with `Worksheet_Change` event – Siddharth Rout Apr 04 '16 at 15:35
  • Checking for an intersect doesn't guarantee that *all* cells in `Target` are within `keyCells`, only that at least *one* of them is.... – Tim Williams Apr 04 '16 at 16:18

0 Answers0