VBA - Range Exclusion - Exclure un Range d'un autre Range [Supprimé]

patricktoulon

XLDnaute Barbatruc

• 2 Décembre 2023

• #16

re
je viens de faire mes tests
le resultat est bien plus propre
le concat des address et opéré tandis qu'avec ta version il n'y a pas de concat c'est cel par cel
regarde ce que je me suis amusé à tester c'est bien tordu quand même hein
et bien quand il faut cacatainer ca concat
ce qui est bleu et jaune c'est la rng1
ce qui est jaune c'est la rng 2

 

Dudu2

XLDnaute Barbatruc

• 2 Décembre 2023

• #17

Alors ce test est révélateur et on peut en tirer quelques enseignements.
D'une part la méthode traditionnelle améliorée (par les Unions par adresses) est la plus efficace.
Et même plus efficace que la méthode par Map Range sur Table.
Mais je sais pourquoi. C'est parce que le Map Range sur Table n'essaie pas de trouver dans la table des Ranges complets et travaille cellule par cellule. Je vais tenter d'améliorer ça mais c'est difficile à coder.

 

Pièces jointes

• Classeur1.xlsm

  43 KB · Affichages: 0

patricktoulon

XLDnaute Barbatruc

• 2 Décembre 2023

• #18

re moi je fait simple
1° je boucle sur toute les cells de rng1
2° je boucle sur tout les areas de la rng2
si intersect ben non sinon oui

en sortie de boucle area si non alors
si rgnfinale n'existe pas set rngfinale=cell
si elle existe set rngfinale=union(rngfinale,cel)
terminé c'est pas compliqué
et pour info j' ai recopié tes address de ton userform dans le mien
et je suis largement plus rapide et le résultat et beaucoup plus propre en terme d'area concatainée

 

Pièces jointes

• Range Exclusion.xlsm

  67.7 KB · Affichages: 0

Dudu2

XLDnaute Barbatruc

• 2 Décembre 2023

• #19

re moi je fait simple
1° je boucle sur toute les cells de rng1
2° je boucle sur tout les areas de la rng2
si intersect ben non sinon oui

Cliquez pour agrandir...

Tu testes avec des petits Ranges faits à la mano, alors toutes le méthodes fonctionnent.

Ça ne fonctionne pas pour de gros Ranges. C'est pour ça que je me suis penché sur la question à la suite de problème de lenteur que j'ai eu.

Dans le test d'exclusion (voir fichier Post #17), pour la construction des Ranges si tu passes de For i = 1 To 1200 où cette méthode prends chez moi plus de 8 secondes à For i = 1 To 2000, cet méthode va prendre 38 secondes.
Si tu passes à For i = 1 To 5000, tu peux aller te coucher t'auras le résultat demain matin.

 

Dudu2

XLDnaute Barbatruc

• 2 Décembre 2023

• #20

J'ai finalement codé la reconnaissance des Areas dans la fonction MapTableToRange() pour éviter d'avoir à unir par adresse cellule par cellule. Le résultat est assez positif car pour exclure 1000 Ranges de 2000 Ranges:
1 - La méthode traditionnelle améliorée (la plus performante avant) = 17 secondes.
2 - La méthode par Map Range To Table (avec la modif) = 3 secondes.
Je n'ai même pas testé la méthode traditionnelle car pour ce nombre de Ranges, on y passe la journée ou la nuit.

Je pense que plus les Ranges sont nombreux plus la différence de temps s'agrandit.
Évidemment si tous les Ranges sont de 1 cellule, la 2ème méthode n'apportera rien par rapport à la 1ère.

 

Pièces jointes

• Classeur1.xlsm

  39.7 KB · Affichages: 1

Dudu2

XLDnaute Barbatruc

• 2 Décembre 2023

• #21

Pour exclure 1250 Ranges de 2500 Ranges:
1 - La méthode traditionnelle améliorée (la plus performante avant) = 34 secondes.
2 - La méthode par Map Range To Table (avec la modif) = 4 secondes.

 

Dernière édition: 2 Décembre 2023

Dudu2

XLDnaute Barbatruc

• 2 Décembre 2023

• #22

Pour exclure 1500 Ranges de 3000 Ranges:
1 - La méthode traditionnelle améliorée (la plus performante avant) = 54 secondes.
2 - La méthode par Map Range To Table (avec la modif) = 5 secondes.

 

B

bsalv

XLDnaute Occasionnel

• 3 Décembre 2023

• #23

avec la méthode que j'avais proposé, la macro est encore2 fois plus vite. (plusieurs areas en même temps dans une feuille auxiliaire)

 

Dudu2

XLDnaute Barbatruc

• 3 Décembre 2023

• #24

xxx

 

Dernière édition: 3 Décembre 2023

Dudu2

XLDnaute Barbatruc

• 3 Décembre 2023

• #25

Bonjour,
Peux-tu mettre ta méthode dans le classeur ci-joint STP ?

Révision à la hausse des performance de la méthode Map Range To Table à cause d'un Bug grossier !
Ne pas écrire For Each Area in Range -> Car chaque Area sera une cellule !
Mais écrire For Each Area in Range.Areas.

Pour exclure 1000 Ranges de 2000 Ranges:
1 - La méthode traditionnelle améliorée (la plus performante avant) = 17 secondes.
2 - La méthode par Map Range To Table (avec la modif) = 0.7 secondes.

 

Pièces jointes

• Range Exclusion Test Performance.xlsm

  44.8 KB · Affichages: 3

Dudu2

XLDnaute Barbatruc

• 3 Décembre 2023

• #26

Le problème de la méthode Map Range To Table c'est que la table ne peut pas représenter un Range dont les Areas sont très espacées, par exemple aux 4 coins de la feuille, car ça fait plus de 16 milliards de cellules à représenter et ça part en vrille.
Donc dans ce cas, il faut faire fallback sur la méthode traditionnelle améliorée.

 

patricktoulon

XLDnaute Barbatruc

• 3 Décembre 2023

• #27

bonjour bonjour

 

patricktoulon

XLDnaute Barbatruc

• 3 Décembre 2023

• #28

@Dudu2
je vais te demander de tester cette sub
de bien observer le msgbox
et de me dire ce qui ne va pas
mais surtout prends ton temps

VB:

Sub test()
    Dim r As Range
    Dim r1 As Range
    Dim r2 As Range
    Dim t As Single
    Dim texte As String
    Cells.Interior.Color = xlNone
    
    Set r1 = [D3:J9]
    Set r2 = [E1:E15]
    
    r1.Interior.Color = RGB(0, 200, 255)
    r2.Interior.Color = RGB(0, 255, 200)
    
    texte = "Range 1 = " & r1.Address(0, 0) & vbCrLf & "Range 2 = " & r2.Address(0, 0) & vbCrLf

    t = Timer
    Set r = ExclusionTraditionnelleAméliorée(r1, r2)
    texte = texte & "Exclusion traditionnelle améliorée : " & Format(Timer - t, "#0.00 sec") & vbCrLf & _
            GetAddressFromRange(r) & vbCrLf & "********************************************" & vbCrLf

    t = Timer
    Set r = ExclusionTraditionnelleAméliorée(r1, r2)
    texte = texte & "Exclusion traditionnelle améliorée : " & Format(Timer - t, "#0.00 sec") & vbCrLf & _
            GetAddressFromRange(r) & vbCrLf & "********************************************" & vbCrLf


    'Exclusion par la méthode Map Range to Table du Module_RangeFunctions
    t = Timer
    Set r = Exclusion(r1, r2)
    texte = texte & "Exclusion Map Range to Table : " & Format(Timer - t, "#0.00 sec") & vbCrLf & _
            GetAddressFromRange(r) & vbCrLf & "********************************************"

    MsgBox texte

End Sub

 

patricktoulon

XLDnaute Barbatruc

• 3 Décembre 2023

• #29

re
Alors @Dudu2 tu a trouvé ce qui ne va pas ?

 

B

bsalv

XLDnaute Occasionnel

• 3 Décembre 2023

• #30

vous testez avec 2 plages assez petit, si on prend des plages plus grand ou complexe, vous perdez votre temps en buvant du café en attendant ...
union commence à ralentir après 100 fois/cellules

donc avec de plages modifiées !!!

 

Dernière édition: 3 Décembre 2023