Count unique records in a filtered range

Hello jiminic,

Here is macro that will return the row count for a range after it has been filtered.

Function GetUniqueCount(Rng As Range) As Long

  Dim Cnt As Long
  Dim RA As Variant
    
    On Error Resume Next
      Set Rng = Rng.SpecialCells(xlCellTypeVisible)
      If Err = 0 Then
        For Each RA In Rng.Areas
          Cnt = Cnt + RA.Rows.Count
        Next RA
      Else
         Err.Clear
         On Error GoTo 0
      End If
    
    GetUniqueCount = Cnt
    
End Function

Sincerely,
Leith Ross

Thank you Leith, how do I make this work though? I can not find the macro name in the list to apply it to a button.

Hello jiminic,

The only macros that show up in the Macro List (ALT+F8) are Functions and Subs which don't take arguments. This Sub requires a Range Argument and must be run from within VBA. It cannot be converted to a Function and used as a UDF because Excel will always return the count of all rows in the given range, and not just the filtered ones. It would help to see your workbook.

Sincerely,
Leith Ross