Use SUM and FREQUENCY functions to count unique items
 

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

Use SUM and FREQUENCY functions to count unique items
 

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

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

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