Vertraging in macro door SUMIFS of ENTIREROW.DELETE

[BarbaraC]

Beste,

Ik heb een macro die een lijst van data moet bewerken en filteren.

Er zijn echter een aantal stappen die erg traag verlopen, waaronder:
- gebruik van formule 'sommen.als() (of sumifs())
- verwijderen van rijen van een gefilterd bereik (nl. offset(1).entirerow.delete)

Is uiteraard te wijten aan de meer dan 100.000 rijen in mijn data.
Oorspronkelijk was mijn macro helemaal anders, met veel lussen (For .. Next ... etc.), wat toen al traag werkte, maar dit is echter niet veel beter...

Zie bijlage voor mijn macro. Omdat mijn bestand te groot was, heb ik wel het aantal rijen beperkt tot ong. 50.000 rijen.

Als er gezocht wordt op "sumifs" en "entirerow.delete" weet je waar de vertragingen zitten.
Iemand die mij kan helpen om de macro vlotter te laten uitvoeren?
Een alternatief voor de sumifs of entirerow.delete?

Geef gerust uitleg, ik leer graag bij.

Alvast bedankt,
Mvg, Barbara

 

[peter59]

Hallo,

De macro werkt bij mij helaas niet dus kan ook niet zien hoe eventueel de totale layout uit komt te zien.
Zelf denk ik dat je veel beter uit bent met de in excel voorhande zijnde power query.

 

[snb]

Laat eens zien in een 2e werkblad hoe de output eruit moet zien.

Als ik het goed zie probeer je de draaitabel opnieuw uit te vinden.

 

[peter59]

@snb
Je weet dat ik zo goed als geen VBA kennis heb maar ik kan wel zien dat het geheel van een externe locatie wordt binnen gehaald.
Dat gaat dus heel makkelijk met power query en dan daar enkele, eventuele berekeningen op los laten en klaar is Barbara. Dus niks VBA. Dus ook zo goed als geen vertragingen.

 

[snb]

Maar wat doen die gegevens in het werkblad daar dan ?

 

[peter59]

Aanname is dat dit een voorbeeldje van Barbara is zoals het e.e.a. wordt binnen gehaald.
De output zoals jij ook al vroeg, ontbreekt. Tevens krijg ik bij het starten van de macro al een fout en dan hoort het voor mij wat betreft de macro al op.

PS
Heb je de code al eens bekeken? Ja? Wat is dan je conclusie?

 

[snb]

Die staat in mijn eerste bericht.

 

[peter59]

Heb je de code al eens bekeken? Ja? Wat is dan je conclusie?

Je geeft geen antwoord op mijn vraag!!!

 

[cow18]

@peter59, je kan beginnen met dat ene blad te hernoemen naar "SavedReport2031"
Het lijkt me ook eerder het warm water opnieuw uitvinden,
@BarbaraC welke is je excelversie (hopelijk 365) ?
Nog een tip, eerst sorteren en dan de gefilterde lijnen verwijderen levert tijdwinst op. Dus sorteren op weekdag en dan de zaterdagen en zondagen verwijderen en daarna sorteren op dagresultaat en dan de TOTAFW verwijderen. Maar eigenlijk is dat allemaal niet nodig.

 

[OctaFish]

@peter59, je kan beginnen met dat ene blad te hernoemen naar "SavedReport2031"

De vraag is niet van Peter, dus het lijkt mij handiger als je je in de antwoorden alleen richt op Barbara. Peter mag natuurlijk best een eigen vraag stellen . Het draadje wordt nu al behoorlijk onoverzichtelijk door de zijpaadjes. En het zou natuurlijk helpen als barbara ook een keer antwoord gaf.

 

[AHulpje]

Op mijn PC van 59 naar 0,2 seconden, beetje quick and dirty en kan wat eleganter, maar het werkt.

 

[AHulpje]

Nog wat opgeschoond, nu nog maar 0,14 seconden, dus die ruim 100.000 rijen moeten toch wel binnen een halve seconde verwerkt kunnen zijn.

 

[BarbaraC]

Allen, excuseer voor de late reactie. Ik had een aantal dagen verlof, dus had toen snel nog de vraag gesteld, in de hoop dat ik na het verlof onmiddellijk verder kon..

@peter59: mijn excuses, ik was inderdaad - door het kopiëren - vergeten om het tabblad te benoemen naar 'SavedReport2031'. Dan werkt de macro wel.

@snb: ik probeer helemaal geen draaitabellen her uit te vinden. Er moeten - zoals @peter59 opmerkte - een aantal bewerkingen op gebeuren. Dan is PowerQuery inderdaad veel beter.

@snb en @peter59 : weet wel dat hetgeen ik hier breng maar een onderdeeltje is van een veel grotere macro. Alles loopt heel vlot, behalve dit deeltje (vertraging). Ik ken heel goed PowerBI en PowerPivot, maar PowerQuery gebruik ik eigenlijk nooit (nochtans een must, I know..). Ik heb er nooit aan gedacht om macro's te combineren met PowerQuery. Bedankt voor de tip! Ik bekijk dit verder.

@AHulpje: dankjewel voor je moeite. Ik ga eerst met PowerQuery aan de slag, maar ik zal jouw macro zeker ook eens lezen. Ik kan er maar uit leren..

Groeten,
Barbara

 

[peter59]

Ik ken heel goed PowerBI en PowerPivot, maar PowerQuery gebruik ik eigenlijk nooit (nochtans een must, I know..).

PowerQuery is ook een onderdeel van PowerBI en PowerQuery is ook te combineren met PowerPivot.
Dus, mogelijkheden zat dacht ik zo.

 

[snb]

Ik betwijfel of je bekend bent met draaitabellen.

 

[BarbaraC]

Ik betwijfel of je bekend bent met draaitabellen.

Ik waardeer je aanwezigheid op dit forum. Het valt me op dat je al meer dan 19.000 berichten hebt geplaatst, wat getuigt van je actieve betrokkenheid. Anderzijds lijken de berichten die ik tot nu toe van je ontvangen heb, weinig bij te dragen aan de lopende discussies. Niet fijn voor mij, en niet fijn voor anderen.
Zoals het gezegde luidt: "er bestaan geen domme vragen, alleen domme antwoorden." Misschien kan je overwegen om je opmerkingen meer te richten op het geven van inhoudelijke en zinvolle bijdragen. Dankjewel!

 

[BarbaraC]

@peter59 Ik heb al heel wat kunnen vereenvoudigen met uw tip om ook PowerQuery te gebruiken.

Ik zit nog vast op het volgende:
Ik heb een draaitabel in tabblad A: is een lijst van werknemers (ID: werknemernummer) waarbij mijn collega's bepaalde info toevoegen in bestaande kolommen.
Ik heb in tabblad B een lijst van alle werknemers op dit moment in dienst (via PowerQuery data opgehaald: een export via een job uit een softwareprogramma van personeelsdata)
Tabblad A moet aangevuld worden met alle werknemers die erbij gekomen zijn (dus alle werknemersnummers die wel in tabblad B staan, maar niet in tabblad A, moeten in tabblad A aangevuld worden).

Doe ik dat best via macro of kan het ook PowerQuery-gewijs?

 

[peter59]

Zoals je het omschrijft kan dat makkelijk met power query.
Maar een voorbeeldje zoals je het nu al voor elkaar hebt gekregen doet wonderen.
Let op: het bestandje natuurlijk wel ontdoen van gevoelige info.

 

[BarbaraC]

Zoals je het omschrijft kan dat makkelijk met power query.
Maar een voorbeeldje zoals je het nu al voor elkaar hebt gekregen doet wonderen.
Let op: het bestandje natuurlijk wel ontdoen van gevoelige info.

Zie bestand in bijlage.
Voorbeeld forum.xlsm - tabblad 'Lijst Gesprekken'. Dit betreft 'tabblad A' in mijn uitleg.
Tabblad B was vanuit PowerQuery = data vanuit SR 3003-2.xlsx (stond er oorspronkelijk in, maar heb ik moeten wissen wegens te groot bestand om hier up te loaden)

Doel = alle werknemernummers (+ Naam en Voornaam) die in SR 3003-2.xlsx staan, maar niet in tabblad A, moeten in tabblad A aangevuld worden.

 

[peter59]

Ik ga eens kijken of ik iets voor je kan betekenen.
Wordt vervolgt.