Performance issue VB

[Alex B]

Hi,

I´m trying to filter out unique names based on chosen year and organizational unit from a list that contains approx 20000 rows.

Use code below, which is executed with a control button. I experience performance issues, the macro takes 5-10 sec to run.
Is there any better way to do it?

Sub filter()



    Application.ScreenUpdating = False
        
    Sheets("Resurstid").Visible = True
        
    Sheets("Resurstid").Select
    Columns("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "H1:M2"), CopyToRange:=Range("H4"), Unique:=True
        
    Sheets("Analys per konsult").Select
    
                With Sheets("Analys per konsult")


            With .Range("B15:N1000")
            .Clear
            End With
            Dim lastRow2 As Long
            lastRow2 = Sheets("Resurstid").Range("H" & Rows.Count).End(xlUp).Row + 7
            Sheets("Analys per konsult").Range("B13:N13").AutoFill Destination:=Sheets("Analys per konsult").Range("B13:N" & lastRow2)
            End With
    
    Sheets("Resurstid").Visible = False
        
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you tried setting Calculation to Manual at the beginning and Automatic at the end?

 

[Alex B]

Welcome to MrExcel.

Have you tried setting Calculation to Manual at the beginning and Automatic at the end?

Hi Andrew,

thank you!


Your suggestion did not speed up things

 

[Andrew Poulsom]

What are your filter criteria and what do you have in B13:N13 on worksheet "Analys per konsult"?

 

[Alex B]

What are your filter criteria and what do you have in B13:N13 on worksheet "Analys per konsult"?

The list from which I try to filter unique resource names looks like this

[TABLE="width: 356"]
<tbody>[TR]
[TD="width: 93, bgcolor: transparent"]Resource[/TD]
[TD="width: 93, bgcolor: transparent"]Assignment[/TD]
[TD="width: 72, bgcolor: transparent"]Year[/TD]
[TD="width: 72, bgcolor: transparent"]Period[/TD]
[TD="width: 72, bgcolor: transparent"]Org.unit[/TD]
[TD="width: 72, bgcolor: transparent"]Hours[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Agneta Friberg[/TD]
[TD="bgcolor: transparent"]Externa Uppdrag[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]8422[/TD]
[TD="bgcolor: transparent, align: right"]91,5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Agneta Friberg[/TD]
[TD="bgcolor: transparent"]Externa Uppdrag[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]8422[/TD]
[TD="bgcolor: transparent, align: right"]99,5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Agneta Friberg[/TD]
[TD="bgcolor: transparent"]Externa Uppdrag[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]8422[/TD]
[TD="bgcolor: transparent, align: right"]83,5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Agneta Friberg[/TD]
[TD="bgcolor: transparent"]Externa Uppdrag[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]8422[/TD]
[TD="bgcolor: transparent, align: right"]139,5[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Alex Augustsson[/TD]
[TD="bgcolor: transparent"]Externa Uppdrag[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]8411[/TD]
[TD="bgcolor: transparent, align: right"]160[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Alex Augustsson[/TD]
[TD="bgcolor: transparent"]Externa Uppdrag[/TD]
[TD="bgcolor: transparent, align: right"]2013[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]8411[/TD]
[TD="bgcolor: transparent, align: right"]160[/TD]
[/TR]
</tbody>[/TABLE]


The advanced filter setup is

[TABLE="width: 302"]
<tbody>[TR]
[TD="width: 93, bgcolor: transparent"]Assignment[/TD]
[TD="width: 93, bgcolor: transparent"]Year[/TD]
[TD="width: 72, bgcolor: transparent"]Period[/TD]
[TD="width: 72, bgcolor: transparent"]Org.unit[/TD]
[TD="width: 72, bgcolor: transparent"]Hours[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2014[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]8411*[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Where org.unit is chosen in a drop down.


In B13:N13 on worksheet "Analys per konsult", I have formulas that summarize revenue, gross profit, chargeability
from the first table above and another table. Formulas are SUMIFS, where the filtered names are one of the conditions.

 

[Andrew Poulsom]

What's taking the most time, AdvancedFilter or AutoFill?

 

[Alex B]

What's taking the most time, AdvancedFilter or AutoFill?

How can I check that?

 

[Andrew Poulsom]

Split your procedure into 2 procedures and time them.

 

[Alex B]

Split your procedure into 2 procedures and time them.

It seems that the Autofill is the one taking time...

 

[Andrew Poulsom]

Is this any quicker?

Sheets("Analys per konsult").Range("B13:N13").Copy Sheets("Analys per konsult").Range("B14:N" & lastRow2)