Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Unique AutoFilter (2003)

    I have the following macro that counts visible rows in an autofilter.
    Sub CountVisRows()
    'by Tom Ogilvy
    Dim rng As Range
    Set rng = ActiveSheet.AutoFilter.Range

    MsgBox rng.Columns(1). _
    SpecialCells(xlVisible).Count - 1 _
    & " of " & rng _
    .Rows.Count - 1 & " Records"

    End Sub

    Now I need to count unique items in column A . I need to know how many unique client numbers there are after the data is filtered. The column is sorted in client number order so all duplicates will be together. Thanks for any help you can provide.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Unique AutoFilter (2003)

    Here is a custom function:

    Function CountUniqueVisible(oRange As Range) As Long
    Dim colUnique As New Collection
    Dim oCell As Range
    On Error Resume Next
    For Each oCell In oRange.Cells
    If oCell.EntireRow.Hidden = False Then
    colUnique.Add oCell, CStr(oCell.Value)
    End If
    Next oCell
    CountUniqueVisible = colUnique.Count
    End Function

    You can use it in a formula in a cell:

    =CountUniqueVisible(A2:A100)

    (don't include the field name in the range to be counted)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Unique AutoFilter (2003)

    Thanks Hans - works great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •