AutoFilter

[vmjan02]

I have this code, it is giving me error as "AutoFilter method of Range class failed" i tired a lot not sure as how to fix this.

Sub test()
Dim ws As Worksheet
Dim lRow As Long

Set ws = ThisWorkbook.Worksheets("Weekly RawFile")
lRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row ' Assuming data is in column A

' Apply AutoFilter
If lRow > 1 Then ' Check if there is data in the worksheet
ws.Range("A1:K" & lRow).AutoFilter Field:=5, Criteria1:=Array("<>Internet", "<>More than one service affected", "<>VOIP", "<>Jawwy TV", "<>VAS", "<>IOT Smart Service"), Operator:=xlFilterValues  'Error here
Else
    MsgBox "No data found in the worksheet.", vbExclamation
End If

End Sub

FTTH_Technial_Complaints_Weekly_ReportV2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	WEK	CREADTED_DATE	SR_NUM	PH_NUMBER	SR_AREA	SR_SUB_AREA	COMPLAINT_TYPE	COMPLAINT_SOURCE	X_STC_REGION	X_STC_TECHNOLOGY	CITC_REGION
2	17	21-04-2024	1-1409870298883	125391193	Internet	No Internet connection	Technical	Normal	WESTERN	FTTH	Makkah_Province
3	17	21-04-2024	1-1409870566655	112292969	More than one service affected	All services not working	Technical	Normal	CENTRAL	FTTH	Riyadh_Province
4	18	28-04-2024	1-1410690631638	172200654	E-channel
5	17	21-04-2024	1-1409871555445	112255820	Internet	Slow Browsing	Technical	Normal	CENTRAL	FTTH	Riyadh_Province
6	17	21-04-2024	1-1409872368335	122680312	Internet	No Internet connection	Technical	Normal	WESTERN	FTTH	Makkah_Province
7	17	21-04-2024	1-1409865775992	114546441	Internet	No Internet connection	Technical	Normal	CENTRAL	FTTH	Riyadh_Province
8	17	21-04-2024	1-1409871181824	172201246	More than one service affected	All services not working	Technical	Normal	SOUTHERN	FTTH	Asir_Province
9	17	21-04-2024	1-1409874594052	138176995	More than one service affected	All services not working	Technical	Normal	EASTERN	FTTH	Eastern_Province
10	18	28-04-2024	1-1410690631638	172200654	stc play
11	17	21-04-2024	1-1409876022040	112785992	More than one service affected	All services not working	Technical	Normal	CENTRAL	FTTH	Riyadh_Province
12	17	21-04-2024	1-1409872675926	137210312	Internet	OLO ONT Down	Technical	Normal	East	FTTH	Eastern_Province
13	18	28-04-2024	1-1410690631638	172200654	Voice
14	17	21-04-2024	1-1409875454486	163237349	Internet	No Internet connection	Technical	Normal	CENTRAL	FTTH	Qassim_Province
15	17	21-04-2024	1-1409876852597	133443676	Internet	OLO ONT Down	Technical	Normal	Eastern Region	FTTH	Eastern_Province
16	17	21-04-2024	1-1409875230400	144245080	Internet	Speed Upgrade	Technical	Normal	WESTERN	FTTH	Tabuk_Province
17	17	21-04-2024	1-1409876116433	138092703	Internet	OLO ONT Down	Technical	Normal	East	FTTH	Eastern_Province
18	17	21-04-2024	1-1409877411345	122061064	Internet	No Internet connection	Technical	Normal	WESTERN	FTTH	Makkah_Province
19	17	21-04-2024	1-1409877772102	112320320	More than one service affected	Loss of signal	Technical	Normal	CENTRAL	FTTH	Riyadh_Province
20	17	21-04-2024	1-1409878786957	112482745	More than one service affected	All services not working	Technical	Normal	CENTRAL	FTTH	Riyadh_Province
21	18	28-04-2024	1-1410690631638	172200654	test
22	18	28-04-2024	1-1410690631638	172200654	Qitaf Voucher
23	18	28-04-2024	1-1410690631638	172200654	Service
Weekly RawFile

 

[Artik]

Try:

Sub test_1()
    Dim ws As Worksheet
    Dim oDic As Object 'Scripting.Dictionary
    Dim i As Long
    Dim v As Variant
    Dim vArr As Variant

    'Array of excluded elements
    vArr = Split("Internet,More than one service affected,VOIP,Jawwy TV,VAS,IOT Smart Service", ",")

    Set ws = ThisWorkbook.Worksheets("Weekly RawFile")

    On Error Resume Next
    ws.ShowAllData
    On Error GoTo 0

    If Not ws.AutoFilterMode Then
        ws.Range("A1").AutoFilter
    End If

    v = ws.AutoFilter.Range.Columns(5).Value
    
    If TypeName(v) = "String" Then
        MsgBox "No data found in the worksheet.", vbExclamation
    Else
        Set oDic = CreateObject("Scripting.Dictionary")

        On Error Resume Next
        'Add unique values to the Dictionary
        For i = 2 To UBound(v)
            oDic.Add v(i, 1), 0
        Next i
        
        'Remove excluded elements from the Dictionary
        For i = 0 To UBound(vArr)
            oDic.Remove vArr(i)
        Next i
        
        'Filtered the other elements
        ws.AutoFilter.Range.AutoFilter Field:=5, Criteria1:=oDic.Keys(), Operator:=xlFilterValues
        On Error GoTo 0
    End If

End Sub

Artik

 

[vmjan02]

thanks man this is perfect,. thanks bro