Using worksheet function countif for filtered cells RRS feed

  • Question

  • I am using the code below to loop through a filtered column and collect each name of sales person as it loops through; I am using the SpecialCells(xlCellTypeVisible) which is picking up only the visible rows. I only want the name once so I am using the countif worksheet function. Once I have the name I am storing it in a temp variable (namelist_tmp) and adding a semicolon. The end game is to create a variable (namelist) with each name separated by a semicolon so I can use it in the .to section of some email code I have.

    I am able to loop through and capture the name but when I am trying to assign the temp variable to the final variable I get a Run-time error 5 Invalid Procedure call or argument at the namelist_tmp code: 

    namelist = Left(namelist_tmp, Len(namelist_tmp) - 2)

    I don't think I can use WoksheetFunction.Countif with a filtered range and that is what is probably causing the Run-time error 5 Invalid Procedure call or argument, the debugger stops at the namelist_tmp code.

    Here is my full code:

    Sub filteredstuff()
    Dim lastRow As Long, myrange As Range
    lastRow = Cells(Rows.Count, 2).End(xlUp).Row
    For Each myrange In Range("E11:E" & lastRow).SpecialCells(xlCellTypeVisible)
    'At each name, use COUNTIF to look upwards and see if the name already exists.
    'If not, add name, semi-colon and space to temporary name list
             If WorksheetFunction.CountIf(Range("E11:E" & myrange.Row), _
             Range("E" & myrange.Row)) < 2 Then
                 namelist_tmp = namelist_tmp & Range("E" & myrange.Row).Value & "; "
             End If
    'Strip last semi-colon & space off of temp list
                 namelist = Left(namelist_tmp, Len(namelist_tmp) - 2)
    Next myrange
    MsgBox namelist
    End Sub


    Thursday, May 9, 2019 3:03 AM