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

 

[Alex B]

Slightly... What I experience in general is that when org.units with up to 20-50 names are chosen, the code runs quite ok.
It´s with larger org.units (+100 names) it starts to lag.

 

[Andrew Poulsom]

I expect it's related to the number of formulas and their complexity. But you haven't told me what the formulas are so it's difficult to help further.

 

[Alex B]

Here is an example:

=IF(Vald_månad>12;IF(ISERROR(SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;Uppdragsgrupp;"Externa uppdrag";År_tid;Innevarande_år;Månad_tid;">="&MinMånad;Månad_tid;"<="&MaxMånad)/SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;År_tid;Innevarande_år;Månad_tid;">="&MinMånad;Månad_tid;"<="&MaxMånad));"";SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;Uppdragsgrupp;"Externa uppdrag";År_tid;Innevarande_år;Månad_tid;">="&MinMånad;Månad_tid;"<="&MaxMånad)/SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;År_tid;Innevarande_år;Månad_tid;">="&MinMånad;Månad_tid;"<="&MaxMånad));IF(ISERROR(SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;Uppdragsgrupp;"Externa uppdrag";År_tid;Innevarande_år;Månad_tid;Vald_månad)/SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;År_tid;Innevarande_år;Månad_tid;Vald_månad));"";SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;Uppdragsgrupp;"Externa uppdrag";År_tid;Innevarande_år;Månad_tid;Vald_månad)/SUMIFS(Timmar_tid;Resurs_tid;'Analys per konsult'!B13;År_tid;Innevarande_år;Månad_tid;Vald_månad)))

Fomulas use Named cells and ranges, though in swedish, so I think that they won't help you much. And as you can see above, it's quite long so maybe calculating this and similar formulas in some 1000-2000 cells do take some time...

 

[Andrew Poulsom]

Since you are using SUMIFS you must have Excel 2007 or above, in which case you can use IFERROR instead of IF(ISERROR to avoid having the formula twice. The syntax for:

=IF(ISERROR(Formula),"",Formula)

is:

=IFERROR(Formula,"")