Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|