Help with comparing Variant Array with itself

I think you are using the join function incorrectly. It looks like you are using it like the CONCATENATE() function where you are expecting it to join two strings. According the help file (http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx ), the Join function takes a one dimensional array and joins each element of the array, divided by the assigned delimiter. In observing the "compare" variable in the Locals/Watch window, it appears that VBA is treating compare as a 2D array (even though one dimension is 1 to 1), and not accepting compare as an argument for the join function. I'm not exactly sure what you are doing with the join function (I don't see why you are trying to join the array into a single long text string). If you want to use the join function, you are going to need to do something different in assigning the array to compare.

One observation. It seems like all this is doing is a relatively simple COUNTIF(). U2=1/COUNTIF($A$1:$A$300000,A1) (or whatever the reference is). Depending on where this task fits into the overall project, it might be easier to use a worksheet function rather than VBA.