Delete rows in data where column B doesn't meet criteria

Try change to

Sub test()
    Dim rng As Range, temp, myRng As Range
    With Sheets("team list")
        With .Range("a2", .Range("a" & Rows.Count).End(xlUp))
            temp = .Value
            With .Offset(, 2).Cells(1).Resize(, .Rows.Count)
                .Value = Application.Transpose(temp)
                Sheets("marketing stats").Range("b1").Copy .Offset(-1)
                .Value = .Parent.Evaluate("""<>*""&" & .Address & "&""*""")
                .Cells(2, 1).Value = "*(Specials)*"
                Set rng = .CurrentRegion
            End With
        End With
    End With
    With Sheets("marketing stats").Cells(1).CurrentRegion
        .AdvancedFilter 1, rng
        .Offset(1).EntireRow.Delete
        .Parent.ShowAllData
    End With
    Sheets("team list").[c1].CurrentRegion.Clear
End Sub