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

Resource	Assignment	Year	Period	Org.unit	Hours
Agneta Friberg	Externa Uppdrag	2013	1	8422	91,5
Agneta Friberg	Externa Uppdrag	2013	2	8422	99,5
Agneta Friberg	Externa Uppdrag	2013	3	8422	83,5
Agneta Friberg	Externa Uppdrag	2013	4	8422	139,5
Alex Augustsson	Externa Uppdrag	2013	2	8411	160
Alex Augustsson	Externa Uppdrag	2013	3	8411	160

<tbody>
</tbody>

The advanced filter setup is

Assignment	Year	Period	Org.unit	Hours
	2014		8411*

<tbody>
</tbody>

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)