R
ryguy7272
I was trying to work with the below function, but it is too slow:
=SUMPRODUCT((C5:C15000<>"")/(COUNTIF(C5:C15000,C5:C15000&"")))
Subtotal(103,C5:C15000)
Works alright, but doesn't handle uniques...and it is slow too
Next, I tried this code, and it is certainly fast:
Sub Sub1() ' in sheet module
Dim sCell$, iErr&, iRowV&, iCount&
Dim CollPtr1 As Collection ' pointer to object
Set CollPtr1 = New Collection ' object
For iRowV = 5 To 15000
sCell = Cells(iRowV, 3).Value
If sCell <> "" Then ' ck blank
On Error Resume Next ' don't break
CollPtr1.Add "", sCell
iErr = Err.Number
On Error GoTo 0 ' restore error processing
If iErr = 0 Then iCount = iCount + 1
End If
Next iRowV
Cells(3, 3) = iCount
End Sub
However, it doesn't count unique records in a filtered list. I've seen a
Pivot Table count uniques, when the Pivot Table has a helper column.
http://www.contextures.com/xlPivot07.html#Unique
Is this the best way to go? Code, or a pivot table; In think functions will
be too slow (I need to do this for four columns). I'm looking for the best
solution, in terms of speed/performance.
Thanks,
Ryan---
=SUMPRODUCT((C5:C15000<>"")/(COUNTIF(C5:C15000,C5:C15000&"")))
Subtotal(103,C5:C15000)
Works alright, but doesn't handle uniques...and it is slow too
Next, I tried this code, and it is certainly fast:
Sub Sub1() ' in sheet module
Dim sCell$, iErr&, iRowV&, iCount&
Dim CollPtr1 As Collection ' pointer to object
Set CollPtr1 = New Collection ' object
For iRowV = 5 To 15000
sCell = Cells(iRowV, 3).Value
If sCell <> "" Then ' ck blank
On Error Resume Next ' don't break
CollPtr1.Add "", sCell
iErr = Err.Number
On Error GoTo 0 ' restore error processing
If iErr = 0 Then iCount = iCount + 1
End If
Next iRowV
Cells(3, 3) = iCount
End Sub
However, it doesn't count unique records in a filtered list. I've seen a
Pivot Table count uniques, when the Pivot Table has a helper column.
http://www.contextures.com/xlPivot07.html#Unique
Is this the best way to go? Code, or a pivot table; In think functions will
be too slow (I need to do this for four columns). I'm looking for the best
solution, in terms of speed/performance.
Thanks,
Ryan---