Use SUM and FREQUENCY functions to count unique items
Wow! I didn't know you could do that. Thanks alot, Dave. That worked
beautifully!
Dave Peterson wrote:
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
|