Finding (and removing) duplicates is already a built-in functionality; this code is kind of reinventing the wheel. Making VBA code faster than native functionality, from what I've seen, requires thinking outside the box and manipulating memory locations.
But let's review the code we're looking at.
Kudos for using a meaningful name! This one reads like a noun though; since
Sub procedures typically do something, it's common practice to make their names start with a verb, so
ValidateDuplicates would be a better name. The procedure is also implicitly
Public; consider always using explicit modifiers.
Next we have a handful of declarations, for each of the variables used in the procedure. Again, kudos for explicitly declaring all variables (is
Option Explicit specified?) - but consider declaring them when they're needed, as they're needed. That way it's much harder to accidentally leave unused variables behind, like what happened to
Rule of thumb, if there's a digit at the end of a name, it's usually a bad name.
ws1 could be
But you're not here to hear about variable naming are you.
I'm curious why
Range("g2:g" & lastrow) is good enough to get a range encompassing all cells in column G, but once inside the loop we switch to a very long-winded
Range(Cells, Cells) call instead of just doing
Range("B2:B" & lastrow) like we just did.
Do we really need to
COUNTIF to identify a 2nd or 3rd (or 250th) duplicate for one value?
If we had a data structure we could quickly lookup a value from, we could put the known-dupe value in it, and then only perform the expensive
COUNTIF when we already know we're not looking at a known duplicate value.
Dictionary and its O(1) keyed retrieval sounds like a good tool for this.
So instead of just writing
True into column G, we can store the value of column B into our dictionary, and then the loop can now conditionally evaluate the countif when the dictionary doesn't already contain the value in column B.
Actually, with a dictionary you could make the whole loop much more efficient than that.
Start at row 2, end at lastrow: the number of iterations is known before we even start looping - the loop should be a
For loop, not
Do While. So we loop
i from 2 to N, and at each row we try to add the value of column B into the dictionary. If the value already exists in there (that's O(1); CountIf is O(n)), then we know we have a dupe at that row so we write
True to column G. After the loop, column G identifies all dupes, and the dictionary contains all the unique values. Iterating a variant array of values in-memory instead of worksheet cells would be even faster. You'll find the
Dictionary class in the
Scripting library if you want it early-bound.
But then again, I doubt it would be faster than the native highlight duplicates functionality.