List Unique Values of Visible Cells

[realniceguy5000]

Hi,

Would someone take a look at this script below, I keep getting an error "Unable to get countif property of worksheet function class"

I'm running the script on a workbook that is using auto filter. I'm just looking to list the UniqueValues that are visible in col. "Q".

Thanks for the help, Mike

Sub ListUniqueValues()
 
 Dim SearchRng     As Range
 Dim ResultRng     As Range
 Dim Cel           As Range
 Dim iRow          As Long
 
 'Set SearchRng = Range("Q3", Range("Q3").End(xlDown))
 Set SearchRng = Range("Q3:Q65000").SpecialCells(xlVisible)
 
        'Application.InputBox("Select search range","Find Unique Values", Type:=8)
 Do
    Set ResultRng = SearchRng.Offset(, 1)
        'Application.InputBox("Select results columnar range","Write Unique Values", Type:=8)
 Loop Until ResultRng.Columns.Count = 1
 
 iRow = 0
 For Each Cel In SearchRng
    If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then
       
       iRow = iRow + 1
       If iRow > ResultRng.Rows.Count Then
          MsgBox "Not enough rows in result range to write all unique values", _
                vbwarning, "Run terminated"
          Exit Sub
       Else
          ResultRng(iRow).Value = Cel.Value
       End If
    End If
 Next Cel

End Sub