Avatar billede schris Praktikant
25. marts 2021 - 15:13 Der er 5 kommentarer

VBA - Pivot filter er meget langsom - udregner for hver filtreret item - hvordan undgås dette?

Hej!

Jeg har et Excel regneark, hvor jeg har en større pivottabel. Tabellen skal med en macro kunne filtreres på en liste af kontonumre. Jeg har forsøgt at filtrere på 2 forskellige måder (der begge virker, men er meget langsomme), således at kun en bestemt række konti er stat til "visible". Jeg har forsøgt med at lave en liste i et ark og give den et dynamisk named range, som der så filtreres fra (medtode #1).

Jeg har ligeledes forsøgt a lave et hardcoded arry direkte i min kode (metode #2), men begge har den MEGET irrieterende funktion, at pivottabellen bliver genberegnet for hver arry/liste item - og der er +50 items. Er der en måde at få sat disse filtre samtidig, for derefter at lave en enkelt genberegning??

Jeg har forsøgt med nedenstående 2 macros:

Metode #1:

Sub Test()
    Dim PI As PivotItem
    With Worksheets("Graf_kreditorgæld_001").PivotTables("pivottabel1").PivotFields("GL_AccNo")
        .ClearAllFilters
        For Each PI In .PivotItems
            PI.Visible = WorksheetFunction.CountIf(Range("GældsKontiRange"), PI.Name) > 0
        Next PI
    End With
End Sub

I denne kaldes der et named range i andet worksheet

Metode#2:

Private Sub PivotFilterTest()
    Dim pf As PivotField
    Dim myArray() As Variant
    Dim i As Long

    myArray = Array("42270", "62515", "62520", "72110", "72120", "72140", "72150", "72180", "72181", "72185", "72210", "72220", "72230", "72250", "72260", "72270", "72275", "72280", "72285", "72310", "72315", "72320", "72330", "72340", "73110", "73120", "73121", "73125", "73130", "73131", "73133", "73134", "73140", "73150", "73155", "73160", "73610", "73615", "73620", "73625", "73630", "73635", "73640", "73645", "73650", "73655", "76210", "76220", "76230", "76235", "76240", "76250", "76260", "76265", "76989")
    Set pf = Worksheets("Graf_kreditorgæld_001").PivotTables("pivottabel1").PivotFields("GL_AccNo")
    With pf
        .ClearManualFilter
        .EnableMultiplePageItems = True
        For i = LBound(myArray) To UBound(myArray)
            .PivotItems(myArray(i)).Visible = True
        Next i
    End With
End Sub

Her er kontonumre kodet direkte ind i et array.

ER der nogen der har en idet hvordan man kan sætte filtret FØR der genberegnes??
Avatar billede acore Ekspert
25. marts 2021 - 15:56 #1
Du kan slå genberegning fra i din makro før du kører løkken, og så slå det til igen:

Application.Calculation = xlManual

' gør hvad du skal...

Application.Calculation = xlAutomatic

Det er i hvert fald enkelt at afprøve.
Avatar billede acore Ekspert
25. marts 2021 - 15:58 #2
Måske du også skal slå skærmopdatering fra?

Application.ScreenUpdating = False
...
Application.ScreenUpdating = True
Avatar billede schris Praktikant
25. marts 2021 - 17:20 #3
@acore

Tak for tip - de virkede desværre ikke. Problemet ligger formentlig i, at det kører via et FOR NEXT loop. Jeg ved bare ikke hvordan man kommer uden om det, når man skal sætte filtrene
Avatar billede acore Ekspert
26. marts 2021 - 08:07 #4
Loop'et er i sig selv ikke et problem, men hvis det inde i loopet tager tid, og skal laves mange gange, så ja. Jeg synes ikke antallet af gennemløb forekommer særlig stort, så der må være en anden forklaring.

Spørgsmål 1: Du har lagt begge foreslåede sætninger uden for loopet?

Spørgsmål 2: Hvad er "meget langsom"?

Forslag: Har ikke selv så meget erfaring med vba og pivottables, men prøv med

pt.ManualUpdate=True

før loopet og så sæt til false igen bagefter - føler mig ikke overbevist om, at din Application setting sikrer mog genberegning af pivottables også.
Avatar billede acore Ekspert
26. marts 2021 - 08:08 #5
Undskyld ikke pt - pf selvfølgelig
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester