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





Premium
Claus Thorsgaard skal sætte gang i væksten som ny CEO: Her er hovedopgaverne som topchef i Luxion
Den tidligere Conscia-topchef Claus Thorsgaard skal nu sætte gang i væksten hos den danske it-virksomhed Luxion. "Luxion har et kæmpe uudnyttet potentiale. Ordrerne vælter ind ad døren uden en aktiv salgsindsats, så hvis vi begynder at fokusere på det, så skulle det gerne gå endnu bedre," siger den nye direktør til Computerworld.
CIO
Har du rost din mellemleder i dag? Snart er de uddøde - og det er et tab
Computerworld mener: Mellemledere lever livet farligt: Topledelsen får konstant ideer med skiftende hold i virkeligheden, og moden går mod flade agile organisationer. Men mellemlederen er en overset hverdagens helt med et kæmpe ansvar. Her er min hyldest til den ofte latterliggjorte mellemleder.
Job & Karriere
Eva Berneke stopper som topchef i KMD og flytter til Paris: Her er KMD's nye topchef
Efter syv år på posten som topchef for KMD forlader Eva Berneke selskabet. Nu flytter hun med familien til Paris, hvor hun vil fortsætte sit bestyrelsesarbejde. KMD har allerede afløser på plads.
White paper
Sådan prioriterer IT-sikkerhedschefernes indsatsen i 2021
I denne undersøgelse fra F-Secure giver knap 2.000 ledende personer på tværs af hele Europa deres bud på, hvor de vil prioritere indsatsen i år – og hvad de opfatter som virksomhedernes mest presserende udfordringer på cybersikkerhedsfronten.