CODEWITHSUNDEEP
excelvba
Graham Jones
Graham Jones

Reputation: 23

Adding column check to a Unique Value Function

I have this function I use to loop through a range and count how many unique values there are:

    Function CountUniqueValues(InputRange As Range) As Long
       Dim cl As Range, UniqueValues As New Collection
       Application.Volatile
       On Error Resume Next
       For Each cl In InputRange
            UniqueValues.Add cl.Value, CStr(cl.Value) 
       Next cl
       On Error GoTo 0
       CountUniqueValues = UniqueValues.Count
    End Function

   CountUniqueValues(Range("B1:B100"))

I need to add in the for loop a check to another column:

If A1 = "P" Then
   UniqueValues.Add cl.Value, CStr(cl.Value)
End If

Obviously col A would have to increment as col B does. Any ideas?

Upvotes: 2

Views: 298

Answers (1)

Lance Roberts
Lance Roberts

Reputation: 22842

Use:

If Range("A" & c1.Row) = "P" Then

Upvotes: 1

Related Questions