VBA Count Unique Values

Hi, I wonder whether someone may be able to please.

I'm trying to put together a small script which counts the unique values in a given column upon certain critera being met.

Using an example I found here: https://groups.google.com/foru…l.programming/Ht4z85p80uU I've put together the following code:

Sub countunique()
Dim c As Range, UC As New Collection
   On Error Resume Next
   Sheets("All Data").Select
   LR = Cells(Rows.Count, "F").End(xlUp).Row
    For Each c In Range("F8:F" & LR)
    If c.Offset(, -3) = "Consultancy & Requirements" And c.Offset(, -4) = "IDEA" And c.Offset(, 9) = "Yes" Then
        UC.Add c.Value, CStr(c.Value)
        End If
        Next c
Sheets("High Level Figures").Select
Range("K8") = UC.Count
End Sub

Looking in the sheet "All Data", the code basically searches column C 'offset(, -3)' for the value of "Consultancy & Requirements", in column B 'offset(,-4) column O for the value of "IDEA" and 'offset(,9)' for the value of "Yes".

Where these values are found I'm trying to count the unique values which contain a mixture of text and number values from column F starting at row 8 and then paste the figure in K8 on the sheet "High Level Figures".

Unfortunately, and I'm a little unsure why, the correct number of records are not being extracted.

I just wondered whether someone may be able to look at the code I've written and let me know where I've gone wrong.

Many thanks and kind regards

Re: VBA Count Unique Values

Hi snb, thank you for taking the time to reply to my post.

I hadn't thought of advanced filter. I'll have a look at this though.

All the best and kind regards

Re: VBA Count Unique Values

Hi Kenneth Hobson, thank you for taking the time to reply to my post.

Unfortunately I'm unable to post the file because of it's confidential content, but if you say that the logic is correct, I'll have a look at this again to make sure the logic matches the requirements.

Thank you also for the tip.

All the best and kind regards