VBA WorksheetFunction to Count the number of unique values in a range

[longbow2000]

Good Day

As part of a formula in a cell to count the unique values in a range (“A2:A10”), the following is used:

=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))



I am looking for a similar VBA worksheet function (Application.WorksheetFunction.CountIf????), but instead of referencing the rage as A2:A10, it must be cell address with variables.

As an example (myCell(x),myCol(x)): (myCell(z),myCol(z))

Based on the data layout, I can then run a loop function

Thanks in advance

Mr_Excel.xlsm
	A	B	C
1	LinkID_SectionID
2	D1088_010		# Unique Val
3	D1088_020		4
4	D1088_030
5	D1088_030
6	D1088_030
7	D1088_030
8	D1088_030
9	D1088_030
10	D1088_040
11	D1083_010		2
12	D1083_010
13	D1083_010
14	D1083_010
15	D1083_010
16	D1083_020
WorksheetFunction

Cell Formulas
Range	Formula
C3	C3	=SUM(IF(A2:A10<>"",1/COUNTIF(A2:A10, A2:A10), 0))
C11	C11	=SUM(IF(A11:A16<>"",1/COUNTIF(A11:A16, A11:A16), 0))

 

[Fluff]

Do you need VBA, you can do that with a formula

Fluff.xlsm
	A	B	C
1	LinkID_SectionID		# Unique Val
2	D1088_010		4
3	D1088_020
4	D1088_030
5	D1088_030
6	D1088_030
7	D1088_030
8	D1088_030
9	D1088_030
10	D1088_040
11	D1083_010		2
12	D1083_010
13	D1083_010
14	D1083_010
15	D1083_010
16	D1083_020
17
18
19
Master

Cell Formulas
Range	Formula
C2:C19	C2	=IF(OR(A2="",LEFT(A2,5)=LEFT(A1,5)),"",ROWS(UNIQUE(FILTER(A2:A100,LEFT(A2:A100,5)=LEFT(A2,5)))))

 

[longbow2000]

Hi Fluff

Yes, I had it as formula in a cell, but the range size and variable change daily.

Since my post, I have it now as part of VBA in a loop and the module runs correct, as follows:

Dim myRngE() As Integer, myRngS() As Integer, myUniqueR() As Integer

‘Values for myRngE() and myRngS() obtained earlier in module

‘nrRanges and mySecRng also obtained earlier



'For # Unique Range

ReDim myUniqueR(1 To nrRanges)

For z = 1 To nrRanges

'For # Unique Range

mySecRng = myRngE(z) - myRngS(z) + 1

i = myRngS(z)

For i = myRngS(z) To myRngE(z)

nxtr = i + 1

If ws.Cells(i, 1).Value = ws.Cells(nxtr, 1) Then

mySecRng = mySecRng - 1

End If

Next i

myUniqueR(z) = mySecRng

wsTemp.Cells(z + 1, 7) = myUniqueR(z)

Next z

Thanks
Ivan

 

[Fluff]

Glad you sorted it & thanks for the feedback.