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

 

[dmt32]

Hi

See if this update to your Advanced Filter code resolves your issue

Sub test()
    Dim m           As Variant
    Dim lr          As Long
    Dim rngCriteria As Range, rngCopyTo As Range
    Dim wsData      As Worksheet, wsResult As Worksheet
    
    '-----------------------------------------------------------------------------------------------------------------------------------
    '                                                           SETTINGS
    '-----------------------------------------------------------------------------------------------------------------------------------
    
    'set True to mask (hide) criteria formula result from view
    Const HideCriteria As Boolean = False
    '-----------------------------------------------------------------------------------------------------------------------------------
    
    'set object variables
    With ThisWorkbook
        Set wsData = .Worksheets("data")
        Set wsResult = .Worksheets("result")
    End With
    
    Set rngCopyTo = wsResult.Range("A1:E1")
    
    With wsData
        'copy headers to result sheet
        rngCopyTo.Value = .Range("A1:E1").Value
        'criteria range
        If Not .Cells(2, 11).HasFormula Then
            Set rngCriteria = .[k1:k2]
        Else
            'get next blank cell in criteria range
            Set rngCriteria = .Range("K1").Resize(.Columns(11).Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row + 1)
        End If
        
        'get selected month index
        m = Application.Match(.[j2], Application.GetCustomListContents(xlMonth), 0)
        
        If Not IsError(m) Then
            'check if month already filtered
            lr = wsResult.Cells(wsResult.Rows.Count, "A").End(xlUp).Row
            If wsResult.Evaluate("count(if(month(a1:a" & lr & ")=" & m & ",1))") Then
                MsgBox .[j2] & " Is already filtered", 48, "Month Already Filtered"
                Exit Sub
                
            Else
                'add criteria to range end
                rngCriteria(rngCriteria.Cells.Count).Formula = "=month(A2)=" & m
                'mask criteria formula result
                rngCriteria.Font.ColorIndex = IIf(HideCriteria, xlNone, xlAutomatic)
                
                'filter to results sheet
                .Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, _
                CopyToRange:=rngCopyTo, Unique:=False
                
            End If
            
        Else
            'month name error - inform user
            MsgBox .[j2] & Chr(10) & "Invalid Entry", 48, "Error"
        End If
        
    End With
    
End Sub

Note the change in how the code works for the Criteria Range.

Updated code ADDS each month you want to filter to the range to build a permanent list of all months you require to be filtered to the results sheet.
I have given you the ability to mask (hide) the criteria list result if required.

I have only lightly tested on your sample data & seemed to work & does what you want.



Dave

 

[abdelfattah]

Thanks Dave for another choice.

I have only lightly tested on your sample data & seemed to work & does what you want.

not completely .
based on original code should arrange months from small to big .
duplicates dates for MAR month will be under each other of them consecutively even if they are not arranged in DATA sheet.

by the way I wanted how the code should be with old version as RoryA said parameters will be different . so I have really curiosity how the parameters changes with old version . I'm talking about post#3.
thanks.

 

[dmt32]

not completely .
based on original code should arrange months from small to big .

It was just an idea but I did not consider the sorting of the data as I only looked to see if could resolve your speed issue but this is only something you can determine with your real data.
Simple way to resolve sort order would be to sort the master data prior to running the code but if do not want to do that then just need to add bit of code to sort the result.

Dave

 

[abdelfattah]

Simple way to resolve sort order would be to sort the master data prior to running the code but if do not want to do that then just need to add bit of code to sort the result.

I know .
just I would inform you how code works.