why the code is slow despite of using advanced filter with simple data

[abdelfattah]

Hello,
I don't know why the code is slow using advanced filter despite of my data is not big !
what happens if the data big , what if happens then?!
the code should filter data based on each month in J2 cell

Sub test()
    Dim myMonths, x, r As Range, temp
    myMonths = "janfebmaraprmayjunjulaugsepoctnovdec"
    With Sheets("data")
        Set r = .[k1:k2]
        temp = r.Value: r.ClearContents
        x = Fix(InStr(1, myMonths, .[j2], 1) / 3 + 1)
        If x < 1 Then
            MsgBox "Invalid Entry", , .[j2]: Exit Sub
        Else
            If Sheets("result").Evaluate("count(if(month(a:a)=" & x & ",a:a))") Then
                MsgBox .[j2] & " is already filtered": Exit Sub
            End If
        End If
        r(2).Formula = "=month(a2)=" & x
        With .Cells(1).CurrentRegion
            .AdvancedFilter 1, r
            .Offset(1).Copy _
            Sheets("result").Range("a" & Rows.Count).End(xlUp)(2)
        End With
        If .FilterMode Then .ShowAllData
        r.Value = temp
    End With
End Sub

COPY FILTERD MONTH.xlsm
	A	B	C	D	E	F	G	H	I	J
1	CODE	ITEM	TYPE	ORIGIN	Q					SEARCH
2	01/03/2020	VB23	VBG	JHGFF	100.000					MAR
3	10/05/2020	BN12	FDS	MMM	10.000
4	11/03/2020	CV69	JKI	VV	20.000
5	22/03/2020	AQ2	KJJ	NN	30.000
6	11/08/2020	YU12	BNB	DD	30.000
7	30/06/2020	YU13	BNB	DD	31.000
DATA

Cells with Data Validation
Cell	Allow	Criteria
B2	Custom	=ISERROR(SEARCH("above";E7))

result when write month in J2 then will copy to the bottom in result sheet.

COPY FILTERD MONTH.xlsm
	A	B	C	D	E
1	CODE	ITEM	TYPE	ORIGIN	Q
2	01/03/2020	VB23	VBG	JHGFF	100.000
3	11/03/2020	CV69	JKI	VV	20.000
4	22/03/2020	AQ2	KJJ	NN	30.000
RESULT

Cells with Data Validation
Cell	Allow	Criteria
B2	Custom	=ISERROR(SEARCH("above";E7))

I use advanced filter without loop should really fats that what I think.
I hope to experts help me to find efficient way using advanced filter to make fast and deal with big data(could be 20000 rows in DATA sheet).

 

[abdelfattah]

I become curiosity despite of I got solution by Dante .
ok I will inform you tomorrow.

 

[abdelfattah]

The version shouldn't be an issue

you're right.
still show error !

 

[MARK858]

The only error I got with that code (I ran the whole code this time ) with the data you posted wasn't on that line, it was on the line

shR.ShowAllData

which I initially edited out

Not sure why the last 2

Application.ScreenUpdating = False

are there

 

[abdelfattah]

The only error I got with that code (I ran the whole code this time ) with the data you posted wasn't on that line, it was on the line

shR.ShowAllData

which I initially edited out

Not sure why the last 2

Application.ScreenUpdating = False

are there

I don't surprise if the code works for Dante, you in the beginning .

I 'm not sure if the problem from EXCEL setting or windows system !

 

[MARK858]

Can you post the actual file as you have it and I'll look at it later in the day.

 

[abdelfattah]

ok here is the file
Dropbox

 

[MARK858]

Afraid I have run the code on the file you supplied and the only error I get is on the shR.ShowAllData line, I can't reproduce the error you are getting

 

[abdelfattah]

this is my bad lucky ,Mark.
any way thanks for your time.

 

[RoryA]

The Autofilter only used to have 5 arguments, but now has an additional 6th for SubField. Dante's code provides 6, so won't work on older versions.

 

[abdelfattah]

Dante's code provides 6, so won't work on older versions.

I suspected that .
I used for 2010,2016.