WorksheetFunction.countif criteria not working

[mecerrato]

I am using the following code to create an email list of unique emails. The list has a lot of duplicates, but I only want them once. Some of the rows have not been assigned an email so they show as "UNASSIGNED" <unassigned>and I want to ignore those.


I have used this in another sheet where it is working well, the difference is that on this new application I need to copy the data to a temporary location, because it is filtered and the CountIf does not work on filtered rows.


The code is suppose to ignore the criteria for the text "UNASSIGNED"<unassigned>.


I first use the CountIf to get the loan officer email list (MLO list). That works fine I then have code to get the Processor list that has some rows with word "UNASSIGNED", this is not working as it should. The code below Processor list is supposed to ignore any value that equals "UNASSIGNED"<unassigned>, but it doesn't:

Sheets(2).Cells.ClearContents
  lastSrcRw = Sheets("Pipeline").Cells(Rows.Count, 2).End(xlUp).Row
     For Each cell In Sheets("Pipeline").Range("E11:E" & lastSrcRw).SpecialCells(xlCellTypeVisible)
        dstRw = dstRw + 1
        cell.Copy Sheets(2).Range("A" & dstRw)
     Next


'Loop through Sheet2 list, extract unique addresses
  lastTmpRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
     For tmpRw = 1 To lastTmpRw
         If WorksheetFunction.CountIf(Sheets(2).Range("A1:A" & tmpRw), _
            Sheets(2).Range("A" & tmpRw)) < 2 Then
               addylist_tmp = addylist_tmp & Sheets(2).Range("A" & tmpRw).Value & "; "
         End If
     Next tmpRw


'Clean up temp addylist
     addylist = Left(addylist_tmp, Len(addylist_tmp) - 2)
     'MsgBox addylist


'Processor List
Sheets(2).Cells.ClearContents
  lastSrcRw = Sheets("Pipeline").Cells(Rows.Count, 4).End(xlUp).Row
     For Each cell In Sheets("Pipeline").Range("C11:C" & lastSrcRw).SpecialCells(xlCellTypeVisible)
        dstRw = dstRw + 1
        cell.Copy Sheets(2).Range("D" & dstRw)
     Next


'Loop through Sheet2 list, extract unique addresses
  lastTmpRw = Sheets(2).Cells(Rows.Count, 4).End(xlUp).Row
     For tmpRw = 1 To lastTmpRw
         If WorksheetFunction.CountIf(Sheets(2).Range("D1:D" & tmpRw), "<>" & "UNASSIGNED<unassigned>") Then
         If WorksheetFunction.CountIf(Sheets(2).Range("D1:D" & tmpRw), Sheets(2).Range("D" & tmpRw)) < 2 Then
                addylist_tmp2 = addylist_tmp2 & Sheets(2).Range("D" & tmpRw).Value & "; "
         End If
         End If
     Next tmpRw


'Clean up temp addylist
     addylist2 = Left(addylist_tmp2, Len(addylist_tmp2) - 2)

</unassigned></unassigned></unassigned></unassigned>

 

[Fluff]

You're welcome & thanks for the feedback