Search multiple ranges for duplicates

Hello,

I'm trying to search multiple ranges for duplicates, and then display a message box if a duplicate is found. I'm highlighting duplicates via conditional formatting as a backup, but I need a message box too.

The data that I'm trying to check is located in J4:J15, J20:J34, and AF4:AF28. The cells contain event names, and I can't allow duplicate events. If a user types an event name into any of these fields that already exists, I need a message box to appear with a warning. I've seen plenty of code to check one entire column for duplicates, or one range, but I haven't been able to get anything to work for multiple ranges. Can anyone help me out?

It should be straightforward to adapt code covering a single range. Can you post something you've tried?

Here's one that I tried, though it's not my favorite. I didn't like this one because, aside from being unable to make it work for multiple ranges, it brings up two message boxes every time, instead of one. I'm not sure why.

Please Login or Register  to view this content.

I've tried simply editing the range to read "J4:J15,J20:J34,AF4:AF28" but it gives me a type mismatch error.

It fires twice because the duplicate value will be found twice (or more). Just to be clear, are you checking for duplicates over the whole of the three ranges?

Does this help ?

Please Login or Register  to view this content.

Ah, that makes sense. Do you have a suggestion on how to avoid the multiple firings?

Yes, I need it to check to see if any of the values in these three ranges are the same, and throw an error if so. But only these ranges, not the entire columns.

No, that doesn't help, thanks though. I actually tried what you had, and changed it to see if it made a difference. I've tried having this as a separate sub and doing it withing the worksheet_change, and neither makes a difference either.

Try this

Please Login or Register  to view this content.

That seems to work perfectly, thank you!!

My pleasure. Actually not quite as straightforward as I originally imagined!