Count Unique Values

  • Hello,
    I have tried for the better part of 2 days to find some code for counting unique values in a range and so far nothing has worked. I suspect it has more to do with the code I have to identify the range containing the values than the coding that I found.


    The brief explanation as to why I have this code to find the ranges is that we have reports generated daily that were designed poorly up front but we (the end users) have no control for having them improved.


    The end result of the following code is to identify the range for the count for each worksheet (this portion working). So rng4 will result in something like $E$34:$E$406.


    What I am looking for is code that can reference "rng4" and count the unique values therein. The results of that count will then be placed in cell B1.


    I would appreciate any help/advice that I can get on this.


    Thanks



  • Re: Count Unique Values


    Try this. The function creates an AdvancedFilter copy of just the unique values of rng4 in an out of the way place (the right-most column of the worksheet), counts the values, and then clears the data before returning the number back to Range("A1").



    The only change I made in your sub was

    Code
    1. ws.Range("A1") = countUniques(rng4)
  • Re: Count Unique Values


    Thanks shknbk2, this code looks promising but unfortunately does not work as intended. That being said, I don't think the problem is in your code I think it is in mine. Specifically as it pertains to rng4. I don't think it is correctly translating as a range. Through DIM I set it as variant because the other options I tried just popped errors. Your code as you have it gave a type mismatch error on rng4 so I added .Address to it and that seems to have resolved that issue and that is the only change I made. I reworked the error handler so that it should only handle 1 specific error to see if any others would pop but no luck. I added debug.print but all I got was 5 lines containing only a single space each. At this point I am not getting any errors but I am not getting any results either.


    Any other advice?


    Thanks

  • Re: Count Unique Values


    Try this to make it a range:


  • Re: Count Unique Values


    That is how I first tried it and the results are that rng4 = nothing. If I modify the code to be "Set rng4 =" then I get a type mismatch error on the second & portion of " Set rng4 = rng2.Offset(1, 0).Address & ":" & rng3.Address " and if I modify that it just cascades into 1 error after another. Since starting this reply I have tried a few more things including multiple different DIM options but I have come full circle, "Variant" is the only one that actually allows rng4 to get a value. And the value is correct; just seemingly not usable.


    Thanks

  • Re: Count Unique Values


    Hello, Please disregard my last post. I missed a couple of the changes you made. Once I had it all in it worked like a charm. I can't thank you enough.