Count Text & Unique numbers in Text Box

[l4ya]

Hello,

In my attached sheet there are two textboxes. In textbox1, I would like to see count of unique numbers visible numbers in column D, and in textbox2, i would like to see count of visible text "Yes" from column C. Can someone help? Thanks.

[rylo]

Hi

In a general module in the workbook, enter the following code

Function mycountif(rng As Range, x) As Long
  cntr = 0
  For Each ce In rng
    If ce.Value = x And ce.EntireRow.Hidden = False Then cntr = cntr + 1
  Next ce
  mycountif = cntr
End Function

Function mycountuniq(rng As Range) As Long
  Dim nodupes As New Collection
  For Each ce In rng
    On Error Resume Next
    If ce.EntireRow.Hidden = False Then
      nodupes.Add Item:=ce.Value, key:=CStr(ce.Value)
    End If
    On Error GoTo 0
  Next ce
  mycountuniq = nodupes.Count
End Function

Then
C1: =mycountif(C3:C34,"Yes")
D1: =mycountuniq(D3:D34)

When you change your filters, these should give you the results you are chasing.

If you then put in a sheet event (right click on the sheet tab, select view code)

Private Sub Worksheet_Calculate()
  TextBox1.Value = Range("C1").Value
  TextBox2.Value = Range("D1").Value
End Sub

then the results of the UDFs will be put into your textboxes.

HTH

rylo