One way is to just let excel calculate it for you:

Option Explicit
Sub testme()
Dim sh As Worksheet
Dim rng As Range
Dim myFormula As String

Set sh = Worksheets("Compiled Totals")

With sh
Set rng = .Range(.Cells(9, "C"), .Cells(.Rows.Count, "C").End(xlUp))
End With
myFormula = "sum(n(frequency( " & rng.Address(external:=True) & "," _
& rng.Address(external:=True) & ")0))"

MsgBox Application.Evaluate(myFormula)

End Sub

Connie wrote:

I need to count the number of unique employee numbers in a range of
employee numbers. I can use the following excel function to get the
answer, but how do I include this in sub routine?

+sum(n(frequency(c9:c50,C9:c50)0))

The range will change with each use of the spreadsheet, so I need to
detect the range of values (first row will always be 9, last row must
be determined). I can do that part:

Set sh = Worksheets("Compiled Totals")
Set rng = sh.Range(sh.Cells(9, "C"), _
sh.Cells(sh.Rows.Count, "C").End(xlUp))

I'm not sure how to determine the unique rows once I've determined the
range.

Any help would be appreciated. Thanks.
Connie

--

Dave Peterson