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---


--
RyGuy

Perhaps in place of:
If sCell <> "" Then
we use:
If sCell <> "" And Cells(iRowV, 3).Rows.Hidden=False Then


This will force the code to ignore the items that the filter has hidden.
--
Gary''s Student - gsnu200814


"ryguy7272" wrote:

> 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---
>
>
> --
> RyGuy

Unreal!! Perfect, too!!
Thanks GS!!!
Ryan---

--
RyGuy


"Gary''s Student" wrote:

> Perhaps in place of:
> If sCell <> "" Then
> we use:
> If sCell <> "" And Cells(iRowV, 3).Rows.Hidden=False Then
>
>
> This will force the code to ignore the items that the filter has hidden.
> --
> Gary''s Student - gsnu200814
>
>
> "ryguy7272" wrote:
>
> > 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---
> >
> >
> > --
> > RyGuy