VBA Script seems working at first glance, but does not..

Hi all,

I have the below VBA script that should apply a filter on my worksheet, and at first it seems to be working fine, but when doind a little double check the file after the filter has been applied contains additional 2 values from column C, namely "98631011" and "87108890" that are not in the search criteria .. Can anyone help me figure out how to fix this so that the filter actualy filter only the correct criterias and not additional?


Attachment 22582

Code:

---

Sub ShowMatches()
    Dim r As Long
    Dim LastRow As Long
    Dim SearchCriteria As String
    Dim arr() As String
    Dim i As Long
    Dim HideRow As Boolean

    SearchCriteria = ("1433;56827013;40632454;1007;47312665;1348;86602530;21941628;30605726;33150026;33790010;88826677;56631022;88383800;23706949;35356287;32545500;33364040;33179800;26174532;46368668;20329969;28820430;39692100;40885225;70151400;21222364;86276500;86103700;32180385;38696688;86127912;42404766;25648828;22732176;87430238;77666001;55868182;33110775;21439583;1029;3545621826;33696007;35813500;86175455;33480500;49131449;69120717;33181030;33305522;33161672;1046;1011;33113311;33470707;1088;33382888;1049;60161214;23256057;86117794;40748780;30710003;1418;1417;58263200;58200115;25858987;21230742;86127916;1390;21842176;1446")
    If SearchCriteria = "" Then Exit Sub
    Application.ScreenUpdating = False
    arr = Split(SearchCriteria, ";")
    LastRow = [D65536].End(xlUp).Row + 1
    For r = LastRow To 2 Step -1
          For i = LBound(arr) To UBound(arr)
              If InStr(1, Cells(r, "C"), arr(i)) > 0 Then
                    HideRow = False
              End If
          Next
          Rows(r).Hidden = HideRow
          HideRow = True
    Next
    Application.ScreenUpdating = True
End Sub

---

Best Regards
Stig

This is because you are using the Instr function which will return 5 for the item "1011" in string "98631011". You must add a test for the length of both strings if you want to test for an exact match or simply use:

Code:

---

If CStr(Cells(r, "C")) = arr(i) Then

---

Hi Jan

Thank you so much .. it seems to have helped :clap: .. I just need to do some more testing, but at first run it looks perfect :-)