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

[StigK]

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?


22582



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;331500 26;33790010;88826677;56631022;88383800;23706949;35356287;32545500;33364040; 33179800;26174532;46368668;20329969;28820430;39692100;40885225;70151400;212 22364;86276500;86103700;32180385;38696688;86127912;42404766;25648828;227321 76;87430238;77666001;55868182;33110775;21439583;1029;3545621826;33696007;35 813500;86175455;33480500;49131449;69120717;33181030;33305522;33161672;1046; 1011;33113311;33470707;1088;33382888;1049;60161214;23256057;86117794;407487 80;30710003;1418;1417;58263200;58200115;25858987;21230742;86127916;1390;218 42176;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

[Jan Karel Pieterse]

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:


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

[StigK]

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 :-)