Accélération de code de comparaison entre 2 feuilles
12 réponses
khinoa
Bonjour à tous,
Je voudrais comparer deux feuilles de 17 000 lignes environ et copier dans
une 3ème feuille les lignes de la feuille 2 dont le contenu de la cellule en
colonne A n'a pas été trouvée dans une autre cellule en colonne A de la
feuille 1. le
nombre de lignes évolue à la hausse chaque jour. Avec Excel 2010 il y a
certes 1 048 576 lignes mais peut-on vraiment les remplir toutes sans
risques de ralentissement notable ?
J'utilise déjà le code suivant qui fonctionne très bien sur des petits
nombres de lignes mais il est lent pour 17 000 lignes (environ 2 à 3
minutes).
For Each c In Range("a2", Range("a1048576").End(3))
Sheets("BASE2").Activate
If Range("a2", Range("a1048576").End(3)).Find(c, Range("a2")) Is Nothing
Then
c.EntireRow.Copy Sheets("BASE3").Range("a1048576").End(3) (2)
End If
Next
End Sub
Je sais que l'on peut utiliser des tableaux en mémoire pour accélérer le
code. Mais les valeurs de la colonne A des deux feuilles à comparer
n'étant naturellement pas sur la même ligne, un code de type
Application.ScreenUpdating = False
For a = 1 To UBound(Tblo1, 1)
If Tblo1(a, 1) <> Tblo2(a,1) Then
Sheets("BASE3").Cells(a, 1) = Tblo1
End If
Next
Next
n'est d'aucune utilité tel quel car il compare deux cellules situées sur la
même ligne ce qui ne sera quasiment jamais le cas dans ma base de données.
Comment faire un mélange des deux codes, si c'est possible, pour avoir
l'exécution la plus rapide ?
Si tu veux entrer une valeur dans la plage A1:A20000, au lieu de boucler, tu peux utiliser une fomrule
'----------------------------------- Sub test() With Feuil3 With .Range("A1:A20000") .Formula = "=Row()" .Value = .Value End With End With End Sub '-----------------------------------
L'utilisation des formules est beaucoup plus rapide qu'une boucle lorsque la chose est possible.
Tu as compris l'essentiel. Il y a beaucoup de façon d'écrire du code. L'essentiel c'est que tu sois à l'aise avec la manière dont tu procèdes. Avec le temps, tu amélioreras sûrement l'efficacité de ton code.
Voici une procédure qui extrait toutes les données des colonnes de la feuil1.
En colonne A, comme tu l'as mentionné, il n'est pas supposé d'avoir de doublon.
S'il y a des cellules vides dans la feuil1, en Feuil3, cela affichera 0 . J'ai ajouté 2 lignes de codes qui les suppriment.
Selon les besoins spécifiques, on peut adapter la formule, ce n'est qu'un exemple!
'----------------------------------------------- Sub Test()
Dim Rg As Range, Rg1 As Range Dim DerCol As Integer, Nb As Long, DerLig As Long With Feuil1 DerCol = .Cells.Find(What:="*", _ LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row T = .Range(.Cells(2, 1), .Cells(DerLig, DerCol)) Set Rg = .Range("A2:A" & DerLig) 'Création de 2 plages nommées pour la formule Rg.Name = "MData" .Range(.Cells(2, 1), .Cells(DerLig, DerCol)).Name = "Plg" End With
With Feuil2 Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row) End With
Application.EnableEvents = False Application.ScreenUpdating = False With Feuil3 With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With 'Nb = Nombre de ligne en Feuil3 Nb = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row - 1 With .Range("B2") .Formula = "=INDEX(plg,MATCH($A2,Mdata,0),COLUMN())" .Resize(, Range("plg").Columns.Count - 1).FillRight With .Resize(Nb, Range("plg").Columns.Count - 1) .FillDown .Value = .Value End With End With 'Suppression des 0 représentant les cellules vides de 'la feuil1 si nécessaire With .UsedRange .Replace 0, "" End With End With 'Suppression des 2 plages nommées ThisWorkbook.Names("Plg").Delete ThisWorkbook.Names("Mdata").Delete Application.EnableEvents = True Application.ScreenUpdating = True
End Sub '-----------------------------------------------
MichD --------------------------------------------------------------- "khinoa" a écrit dans le message de groupe de discussion : 50234437$0$2352$
Bonjour,
J'ai testé tout tes codes, le plus rapide et de loin, comme tu le savais, est celui-ci :
Sub test3()
Dim Rg As Range, Rg1 As Range
Sheets(3).Cells.ClearContents 'Quoique que dans l'immédiat Columns("a").ClearContents suffisait voire Columns("a").Delete
With Sheets(2) Set Rg = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(1) Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(3) With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With End With
Application.EnableEvents = True Sheets(3).Select End Sub
Par contre je ne comprenais pas comment fonctionnait le code et pourquoi il était si rapide. En désactivant la partie With. Resize, j'ai vu la formule inscrite en cellule A2.
=SI(NB.SI(Feuil1!$A$2:$A$17000;2!FeuilA2)=0;Feuil2!A2;""). J'en ai déduis le fonctionnement du Parent. Name qui n'est sans doute pas obligatoire.
J'ai compris que le second With avec Resize et FillDown recopiait la formule dans chaque cellule et affichait les valeurs manquantes à l'endroit où elles manquent. Je pensais que l'utilisation d'une formule retardait le code, je vois qu'il n'en est rien. Puis une recopie de la formule le nombre de lignes nécessaire, .Value = .Value pour transformer la formule en son résultat et un tri pour afficher le résultat en haut de la feuille. Je comprends un peu mieux. Ai-je tout compris ? Les With sont-ils obligatoires ? Ne pourrait-on pas écrire le code sans les With ? Le .Item pourrait-il être vraisemblablement remplacé par Range("a2") ?
Le Parent. Name peut se remplacer par Rg1 tout court si Set = Rg1 .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) devient Set = Sheets(1).Range("A2:A" & Sheets(1).Cells(.Rows.Count, 1).End(3).Row), n'est-ce pas ?
En tout cas, merci déjà pour ton aide précieuse. Ce code a retrouvé 5 valeurs manquantes adjacentes en 6"40 (6 secondes) sur mon PC qui n'est pas une bête de course, suffisant pour de la bureautique basique. J'ai été époustouflé par la précision du code et sa rapidité. Ce sont des instructions (Set, Resize, la formule) que je connaissais séparément mais je ne pensais pas qu'en les combinant il était possible de parvenir à ce résultat. En fait l'élément déterminant était l'usage de la formule. Encore merci.
khinoa
PS : Ce matin j'ai travaillé sur un modèle vierge dans lequel avec une boucle j'ai inséré en colonne A de la feuille 1 un numéro d'ordre jusqu'à 17000 puis copie de la colonne A en feuille 2, suppression de quelques valeurs en feuille 1 et test.
"MichD" a écrit dans le message de groupe de discussion : jvuht8$cig$
Celle-ci ne transfère que les valeurs de la colonne A de la feuil1 vers la feuil3.
As-tu des doublons dans la colonne A de la feuil1 ?
Si tu veux entrer une valeur dans la plage A1:A20000, au lieu de boucler, tu peux utiliser
une fomrule
'-----------------------------------
Sub test()
With Feuil3
With .Range("A1:A20000")
.Formula = "=Row()"
.Value = .Value
End With
End With
End Sub
'-----------------------------------
L'utilisation des formules est beaucoup plus rapide qu'une boucle lorsque la chose est
possible.
Tu as compris l'essentiel. Il y a beaucoup de façon d'écrire du code. L'essentiel c'est
que tu sois
à l'aise avec la manière dont tu procèdes. Avec le temps, tu amélioreras sûrement
l'efficacité
de ton code.
Voici une procédure qui extrait toutes les données des colonnes de la feuil1.
En colonne A, comme tu l'as mentionné, il n'est pas supposé d'avoir de doublon.
S'il y a des cellules vides dans la feuil1, en Feuil3, cela affichera 0 . J'ai ajouté
2 lignes de codes qui les suppriment.
Selon les besoins spécifiques, on peut adapter la formule, ce n'est qu'un exemple!
'-----------------------------------------------
Sub Test()
Dim Rg As Range, Rg1 As Range
Dim DerCol As Integer, Nb As Long, DerLig As Long
With Feuil1
DerCol = .Cells.Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
T = .Range(.Cells(2, 1), .Cells(DerLig, DerCol))
Set Rg = .Range("A2:A" & DerLig)
'Création de 2 plages nommées pour la formule
Rg.Name = "MData"
.Range(.Cells(2, 1), .Cells(DerLig, DerCol)).Name = "Plg"
End With
With Feuil2
Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
Application.EnableEvents = False
Application.ScreenUpdating = False
With Feuil3
With .Range("A2")
.Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _
Rg1.Address & "," & Rg.Parent.Name & _
"!A2)=0," & Rg.Parent.Name & "!A2,"""")"
With .Resize(Rg.Rows.Count)
.FillDown
.Value = .Value
.Sort Key1:=.Item(1, 1), order1:=xlAscending
End With
End With
'Nb = Nombre de ligne en Feuil3
Nb = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row - 1
With .Range("B2")
.Formula = "=INDEX(plg,MATCH($A2,Mdata,0),COLUMN())"
.Resize(, Range("plg").Columns.Count - 1).FillRight
With .Resize(Nb, Range("plg").Columns.Count - 1)
.FillDown
.Value = .Value
End With
End With
'Suppression des 0 représentant les cellules vides de
'la feuil1 si nécessaire
With .UsedRange
.Replace 0, ""
End With
End With
'Suppression des 2 plages nommées
ThisWorkbook.Names("Plg").Delete
ThisWorkbook.Names("Mdata").Delete
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'-----------------------------------------------
MichD
---------------------------------------------------------------
"khinoa" a écrit dans le message de groupe de discussion :
50234437$0$2352$426a74cc@news.free.fr...
Bonjour,
J'ai testé tout tes codes, le plus rapide et de loin, comme tu le savais,
est celui-ci :
Sub test3()
Dim Rg As Range, Rg1 As Range
Sheets(3).Cells.ClearContents
'Quoique que dans l'immédiat Columns("a").ClearContents suffisait voire
Columns("a").Delete
With Sheets(2)
Set Rg = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row)
End With
With Sheets(1)
Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row)
End With
With Sheets(3)
With .Range("A2")
.Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _
Rg1.Address & "," & Rg.Parent.Name & _
"!A2)=0," & Rg.Parent.Name & "!A2,"""")"
With .Resize(Rg.Rows.Count)
.FillDown
.Value = .Value
.Sort Key1:=.Item(1, 1), order1:=xlAscending
End With
End With
End With
Application.EnableEvents = True
Sheets(3).Select
End Sub
Par contre je ne comprenais pas comment fonctionnait le code et pourquoi il
était si rapide. En désactivant la partie With. Resize, j'ai vu la formule
inscrite en cellule A2.
=SI(NB.SI(Feuil1!$A$2:$A$17000;2!FeuilA2)=0;Feuil2!A2;""). J'en ai déduis le
fonctionnement du Parent. Name qui n'est sans doute pas obligatoire.
J'ai compris que le second With avec Resize et FillDown recopiait la formule
dans chaque cellule et affichait les valeurs manquantes à l'endroit où elles
manquent. Je pensais que l'utilisation d'une formule retardait le code, je
vois qu'il n'en est rien. Puis une recopie de la formule le nombre de lignes
nécessaire, .Value = .Value pour transformer la formule en son résultat et
un tri pour afficher le résultat en haut de la feuille. Je comprends un peu
mieux. Ai-je tout compris ? Les With sont-ils obligatoires ? Ne pourrait-on
pas écrire le code sans les With ? Le .Item pourrait-il être
vraisemblablement remplacé par Range("a2") ?
Le Parent. Name peut se remplacer par Rg1 tout court si
Set = Rg1 .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row)
devient
Set = Sheets(1).Range("A2:A" & Sheets(1).Cells(.Rows.Count, 1).End(3).Row),
n'est-ce pas ?
En tout cas, merci déjà pour ton aide précieuse. Ce code a retrouvé 5
valeurs manquantes adjacentes en 6"40 (6 secondes) sur mon PC qui n'est pas
une bête de course, suffisant pour de la bureautique basique. J'ai été
époustouflé par la précision du code et sa rapidité. Ce sont des
instructions (Set, Resize, la formule) que je connaissais séparément mais je
ne pensais pas qu'en les combinant il était possible de parvenir à ce
résultat. En fait l'élément déterminant était l'usage de la formule. Encore
merci.
khinoa
PS : Ce matin j'ai travaillé sur un modèle vierge dans lequel avec une
boucle j'ai inséré en colonne A de la feuille 1 un numéro d'ordre jusqu'à
17000 puis copie de la colonne A en feuille 2, suppression de quelques
valeurs en feuille 1 et test.
"MichD" a écrit dans le message de groupe de discussion :
jvuht8$cig$1@speranza.aioe.org...
Celle-ci ne transfère que les valeurs de la colonne A de la feuil1 vers la
feuil3.
As-tu des doublons dans la colonne A de la feuil1 ?
Si tu veux entrer une valeur dans la plage A1:A20000, au lieu de boucler, tu peux utiliser une fomrule
'----------------------------------- Sub test() With Feuil3 With .Range("A1:A20000") .Formula = "=Row()" .Value = .Value End With End With End Sub '-----------------------------------
L'utilisation des formules est beaucoup plus rapide qu'une boucle lorsque la chose est possible.
Tu as compris l'essentiel. Il y a beaucoup de façon d'écrire du code. L'essentiel c'est que tu sois à l'aise avec la manière dont tu procèdes. Avec le temps, tu amélioreras sûrement l'efficacité de ton code.
Voici une procédure qui extrait toutes les données des colonnes de la feuil1.
En colonne A, comme tu l'as mentionné, il n'est pas supposé d'avoir de doublon.
S'il y a des cellules vides dans la feuil1, en Feuil3, cela affichera 0 . J'ai ajouté 2 lignes de codes qui les suppriment.
Selon les besoins spécifiques, on peut adapter la formule, ce n'est qu'un exemple!
'----------------------------------------------- Sub Test()
Dim Rg As Range, Rg1 As Range Dim DerCol As Integer, Nb As Long, DerLig As Long With Feuil1 DerCol = .Cells.Find(What:="*", _ LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row T = .Range(.Cells(2, 1), .Cells(DerLig, DerCol)) Set Rg = .Range("A2:A" & DerLig) 'Création de 2 plages nommées pour la formule Rg.Name = "MData" .Range(.Cells(2, 1), .Cells(DerLig, DerCol)).Name = "Plg" End With
With Feuil2 Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row) End With
Application.EnableEvents = False Application.ScreenUpdating = False With Feuil3 With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With 'Nb = Nombre de ligne en Feuil3 Nb = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row - 1 With .Range("B2") .Formula = "=INDEX(plg,MATCH($A2,Mdata,0),COLUMN())" .Resize(, Range("plg").Columns.Count - 1).FillRight With .Resize(Nb, Range("plg").Columns.Count - 1) .FillDown .Value = .Value End With End With 'Suppression des 0 représentant les cellules vides de 'la feuil1 si nécessaire With .UsedRange .Replace 0, "" End With End With 'Suppression des 2 plages nommées ThisWorkbook.Names("Plg").Delete ThisWorkbook.Names("Mdata").Delete Application.EnableEvents = True Application.ScreenUpdating = True
End Sub '-----------------------------------------------
MichD --------------------------------------------------------------- "khinoa" a écrit dans le message de groupe de discussion : 50234437$0$2352$
Bonjour,
J'ai testé tout tes codes, le plus rapide et de loin, comme tu le savais, est celui-ci :
Sub test3()
Dim Rg As Range, Rg1 As Range
Sheets(3).Cells.ClearContents 'Quoique que dans l'immédiat Columns("a").ClearContents suffisait voire Columns("a").Delete
With Sheets(2) Set Rg = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(1) Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(3) With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With End With
Application.EnableEvents = True Sheets(3).Select End Sub
Par contre je ne comprenais pas comment fonctionnait le code et pourquoi il était si rapide. En désactivant la partie With. Resize, j'ai vu la formule inscrite en cellule A2.
=SI(NB.SI(Feuil1!$A$2:$A$17000;2!FeuilA2)=0;Feuil2!A2;""). J'en ai déduis le fonctionnement du Parent. Name qui n'est sans doute pas obligatoire.
J'ai compris que le second With avec Resize et FillDown recopiait la formule dans chaque cellule et affichait les valeurs manquantes à l'endroit où elles manquent. Je pensais que l'utilisation d'une formule retardait le code, je vois qu'il n'en est rien. Puis une recopie de la formule le nombre de lignes nécessaire, .Value = .Value pour transformer la formule en son résultat et un tri pour afficher le résultat en haut de la feuille. Je comprends un peu mieux. Ai-je tout compris ? Les With sont-ils obligatoires ? Ne pourrait-on pas écrire le code sans les With ? Le .Item pourrait-il être vraisemblablement remplacé par Range("a2") ?
Le Parent. Name peut se remplacer par Rg1 tout court si Set = Rg1 .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) devient Set = Sheets(1).Range("A2:A" & Sheets(1).Cells(.Rows.Count, 1).End(3).Row), n'est-ce pas ?
En tout cas, merci déjà pour ton aide précieuse. Ce code a retrouvé 5 valeurs manquantes adjacentes en 6"40 (6 secondes) sur mon PC qui n'est pas une bête de course, suffisant pour de la bureautique basique. J'ai été époustouflé par la précision du code et sa rapidité. Ce sont des instructions (Set, Resize, la formule) que je connaissais séparément mais je ne pensais pas qu'en les combinant il était possible de parvenir à ce résultat. En fait l'élément déterminant était l'usage de la formule. Encore merci.
khinoa
PS : Ce matin j'ai travaillé sur un modèle vierge dans lequel avec une boucle j'ai inséré en colonne A de la feuille 1 un numéro d'ordre jusqu'à 17000 puis copie de la colonne A en feuille 2, suppression de quelques valeurs en feuille 1 et test.
"MichD" a écrit dans le message de groupe de discussion : jvuht8$cig$
Celle-ci ne transfère que les valeurs de la colonne A de la feuil1 vers la feuil3.
As-tu des doublons dans la colonne A de la feuil1 ?
J'ai testé ton code ce matin, il a très bien fonctionné et était hyper rapide. Je testerai celui-ci demain ou samedi juste pour le plaisir, car tu as déjà résolu mon problème. J'ai un autre problème qui n'a rien avoir avec celui-ci que je vais poster à part concernant le masquage automatique (si c'est possible) d'un UserForm (avec barre de progression) après qu'il ait affiché 100%.
Encore merci
khinoa
"MichD" a écrit dans le message de groupe de discussion : k00doo$a97$
Si tu veux entrer une valeur dans la plage A1:A20000, au lieu de boucler, tu peux utiliser une fomrule
'----------------------------------- Sub test() With Feuil3 With .Range("A1:A20000") .Formula = "=Row()" .Value = .Value End With End With End Sub '-----------------------------------
L'utilisation des formules est beaucoup plus rapide qu'une boucle lorsque la chose est possible.
Tu as compris l'essentiel. Il y a beaucoup de façon d'écrire du code. L'essentiel c'est que tu sois à l'aise avec la manière dont tu procèdes. Avec le temps, tu amélioreras sûrement l'efficacité de ton code.
Voici une procédure qui extrait toutes les données des colonnes de la feuil1.
En colonne A, comme tu l'as mentionné, il n'est pas supposé d'avoir de doublon.
S'il y a des cellules vides dans la feuil1, en Feuil3, cela affichera 0 . J'ai ajouté 2 lignes de codes qui les suppriment.
Selon les besoins spécifiques, on peut adapter la formule, ce n'est qu'un exemple!
'----------------------------------------------- Sub Test()
Dim Rg As Range, Rg1 As Range Dim DerCol As Integer, Nb As Long, DerLig As Long With Feuil1 DerCol = .Cells.Find(What:="*", _ LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row T = .Range(.Cells(2, 1), .Cells(DerLig, DerCol)) Set Rg = .Range("A2:A" & DerLig) 'Création de 2 plages nommées pour la formule Rg.Name = "MData" .Range(.Cells(2, 1), .Cells(DerLig, DerCol)).Name = "Plg" End With
With Feuil2 Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row) End With
Application.EnableEvents = False Application.ScreenUpdating = False With Feuil3 With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With 'Nb = Nombre de ligne en Feuil3 Nb = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row - 1 With .Range("B2") .Formula = "=INDEX(plg,MATCH($A2,Mdata,0),COLUMN())" .Resize(, Range("plg").Columns.Count - 1).FillRight With .Resize(Nb, Range("plg").Columns.Count - 1) .FillDown .Value = .Value End With End With 'Suppression des 0 représentant les cellules vides de 'la feuil1 si nécessaire With .UsedRange .Replace 0, "" End With End With 'Suppression des 2 plages nommées ThisWorkbook.Names("Plg").Delete ThisWorkbook.Names("Mdata").Delete Application.EnableEvents = True Application.ScreenUpdating = True
End Sub '-----------------------------------------------
MichD --------------------------------------------------------------- "khinoa" a écrit dans le message de groupe de discussion : 50234437$0$2352$
Bonjour,
J'ai testé tout tes codes, le plus rapide et de loin, comme tu le savais, est celui-ci :
Sub test3()
Dim Rg As Range, Rg1 As Range
Sheets(3).Cells.ClearContents 'Quoique que dans l'immédiat Columns("a").ClearContents suffisait voire Columns("a").Delete
With Sheets(2) Set Rg = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(1) Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(3) With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With End With
Application.EnableEvents = True Sheets(3).Select End Sub
Par contre je ne comprenais pas comment fonctionnait le code et pourquoi il était si rapide. En désactivant la partie With. Resize, j'ai vu la formule inscrite en cellule A2.
=SI(NB.SI(Feuil1!$A$2:$A$17000;2!FeuilA2)=0;Feuil2!A2;""). J'en ai déduis le fonctionnement du Parent. Name qui n'est sans doute pas obligatoire.
J'ai compris que le second With avec Resize et FillDown recopiait la formule dans chaque cellule et affichait les valeurs manquantes à l'endroit où elles manquent. Je pensais que l'utilisation d'une formule retardait le code, je vois qu'il n'en est rien. Puis une recopie de la formule le nombre de lignes nécessaire, .Value = .Value pour transformer la formule en son résultat et un tri pour afficher le résultat en haut de la feuille. Je comprends un peu mieux. Ai-je tout compris ? Les With sont-ils obligatoires ? Ne pourrait-on pas écrire le code sans les With ? Le .Item pourrait-il être vraisemblablement remplacé par Range("a2") ?
Le Parent. Name peut se remplacer par Rg1 tout court si Set = Rg1 .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) devient Set = Sheets(1).Range("A2:A" & Sheets(1).Cells(.Rows.Count, 1).End(3).Row), n'est-ce pas ?
En tout cas, merci déjà pour ton aide précieuse. Ce code a retrouvé 5 valeurs manquantes adjacentes en 6"40 (6 secondes) sur mon PC qui n'est pas une bête de course, suffisant pour de la bureautique basique. J'ai été époustouflé par la précision du code et sa rapidité. Ce sont des instructions (Set, Resize, la formule) que je connaissais séparément mais je ne pensais pas qu'en les combinant il était possible de parvenir à ce résultat. En fait l'élément déterminant était l'usage de la formule. Encore merci.
khinoa
PS : Ce matin j'ai travaillé sur un modèle vierge dans lequel avec une boucle j'ai inséré en colonne A de la feuille 1 un numéro d'ordre jusqu'à 17000 puis copie de la colonne A en feuille 2, suppression de quelques valeurs en feuille 1 et test.
"MichD" a écrit dans le message de groupe de discussion : jvuht8$cig$
Celle-ci ne transfère que les valeurs de la colonne A de la feuil1 vers la feuil3.
As-tu des doublons dans la colonne A de la feuil1 ?
J'ai testé ton code ce matin, il a très bien fonctionné et était hyper
rapide. Je testerai celui-ci demain ou samedi juste pour le plaisir, car tu
as déjà résolu mon problème. J'ai un autre problème qui n'a rien avoir avec
celui-ci que je vais poster à part concernant le masquage automatique (si
c'est possible) d'un UserForm (avec barre de progression) après qu'il ait
affiché 100%.
Encore merci
khinoa
"MichD" a écrit dans le message de groupe de discussion :
k00doo$a97$1@speranza.aioe.org...
Si tu veux entrer une valeur dans la plage A1:A20000, au lieu de boucler, tu
peux utiliser
une fomrule
'-----------------------------------
Sub test()
With Feuil3
With .Range("A1:A20000")
.Formula = "=Row()"
.Value = .Value
End With
End With
End Sub
'-----------------------------------
L'utilisation des formules est beaucoup plus rapide qu'une boucle lorsque la
chose est
possible.
Tu as compris l'essentiel. Il y a beaucoup de façon d'écrire du code.
L'essentiel c'est
que tu sois
à l'aise avec la manière dont tu procèdes. Avec le temps, tu amélioreras
sûrement
l'efficacité
de ton code.
Voici une procédure qui extrait toutes les données des colonnes de la
feuil1.
En colonne A, comme tu l'as mentionné, il n'est pas supposé d'avoir de
doublon.
S'il y a des cellules vides dans la feuil1, en Feuil3, cela affichera 0 .
J'ai ajouté
2 lignes de codes qui les suppriment.
Selon les besoins spécifiques, on peut adapter la formule, ce n'est qu'un
exemple!
'-----------------------------------------------
Sub Test()
Dim Rg As Range, Rg1 As Range
Dim DerCol As Integer, Nb As Long, DerLig As Long
With Feuil1
DerCol = .Cells.Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
T = .Range(.Cells(2, 1), .Cells(DerLig, DerCol))
Set Rg = .Range("A2:A" & DerLig)
'Création de 2 plages nommées pour la formule
Rg.Name = "MData"
.Range(.Cells(2, 1), .Cells(DerLig, DerCol)).Name = "Plg"
End With
With Feuil2
Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
Application.EnableEvents = False
Application.ScreenUpdating = False
With Feuil3
With .Range("A2")
.Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _
Rg1.Address & "," & Rg.Parent.Name & _
"!A2)=0," & Rg.Parent.Name & "!A2,"""")"
With .Resize(Rg.Rows.Count)
.FillDown
.Value = .Value
.Sort Key1:=.Item(1, 1), order1:=xlAscending
End With
End With
'Nb = Nombre de ligne en Feuil3
Nb = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row - 1
With .Range("B2")
.Formula = "=INDEX(plg,MATCH($A2,Mdata,0),COLUMN())"
.Resize(, Range("plg").Columns.Count - 1).FillRight
With .Resize(Nb, Range("plg").Columns.Count - 1)
.FillDown
.Value = .Value
End With
End With
'Suppression des 0 représentant les cellules vides de
'la feuil1 si nécessaire
With .UsedRange
.Replace 0, ""
End With
End With
'Suppression des 2 plages nommées
ThisWorkbook.Names("Plg").Delete
ThisWorkbook.Names("Mdata").Delete
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'-----------------------------------------------
MichD
---------------------------------------------------------------
"khinoa" a écrit dans le message de groupe de discussion :
50234437$0$2352$426a74cc@news.free.fr...
Bonjour,
J'ai testé tout tes codes, le plus rapide et de loin, comme tu le savais,
est celui-ci :
Sub test3()
Dim Rg As Range, Rg1 As Range
Sheets(3).Cells.ClearContents
'Quoique que dans l'immédiat Columns("a").ClearContents suffisait voire
Columns("a").Delete
With Sheets(2)
Set Rg = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row)
End With
With Sheets(1)
Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row)
End With
With Sheets(3)
With .Range("A2")
.Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _
Rg1.Address & "," & Rg.Parent.Name & _
"!A2)=0," & Rg.Parent.Name & "!A2,"""")"
With .Resize(Rg.Rows.Count)
.FillDown
.Value = .Value
.Sort Key1:=.Item(1, 1), order1:=xlAscending
End With
End With
End With
Application.EnableEvents = True
Sheets(3).Select
End Sub
Par contre je ne comprenais pas comment fonctionnait le code et pourquoi il
était si rapide. En désactivant la partie With. Resize, j'ai vu la formule
inscrite en cellule A2.
=SI(NB.SI(Feuil1!$A$2:$A$17000;2!FeuilA2)=0;Feuil2!A2;""). J'en ai déduis le
fonctionnement du Parent. Name qui n'est sans doute pas obligatoire.
J'ai compris que le second With avec Resize et FillDown recopiait la formule
dans chaque cellule et affichait les valeurs manquantes à l'endroit où elles
manquent. Je pensais que l'utilisation d'une formule retardait le code, je
vois qu'il n'en est rien. Puis une recopie de la formule le nombre de lignes
nécessaire, .Value = .Value pour transformer la formule en son résultat et
un tri pour afficher le résultat en haut de la feuille. Je comprends un peu
mieux. Ai-je tout compris ? Les With sont-ils obligatoires ? Ne pourrait-on
pas écrire le code sans les With ? Le .Item pourrait-il être
vraisemblablement remplacé par Range("a2") ?
Le Parent. Name peut se remplacer par Rg1 tout court si
Set = Rg1 .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row)
devient
Set = Sheets(1).Range("A2:A" & Sheets(1).Cells(.Rows.Count, 1).End(3).Row),
n'est-ce pas ?
En tout cas, merci déjà pour ton aide précieuse. Ce code a retrouvé 5
valeurs manquantes adjacentes en 6"40 (6 secondes) sur mon PC qui n'est pas
une bête de course, suffisant pour de la bureautique basique. J'ai été
époustouflé par la précision du code et sa rapidité. Ce sont des
instructions (Set, Resize, la formule) que je connaissais séparément mais je
ne pensais pas qu'en les combinant il était possible de parvenir à ce
résultat. En fait l'élément déterminant était l'usage de la formule. Encore
merci.
khinoa
PS : Ce matin j'ai travaillé sur un modèle vierge dans lequel avec une
boucle j'ai inséré en colonne A de la feuille 1 un numéro d'ordre jusqu'à
17000 puis copie de la colonne A en feuille 2, suppression de quelques
valeurs en feuille 1 et test.
"MichD" a écrit dans le message de groupe de discussion :
jvuht8$cig$1@speranza.aioe.org...
Celle-ci ne transfère que les valeurs de la colonne A de la feuil1 vers la
feuil3.
As-tu des doublons dans la colonne A de la feuil1 ?
J'ai testé ton code ce matin, il a très bien fonctionné et était hyper rapide. Je testerai celui-ci demain ou samedi juste pour le plaisir, car tu as déjà résolu mon problème. J'ai un autre problème qui n'a rien avoir avec celui-ci que je vais poster à part concernant le masquage automatique (si c'est possible) d'un UserForm (avec barre de progression) après qu'il ait affiché 100%.
Encore merci
khinoa
"MichD" a écrit dans le message de groupe de discussion : k00doo$a97$
Si tu veux entrer une valeur dans la plage A1:A20000, au lieu de boucler, tu peux utiliser une fomrule
'----------------------------------- Sub test() With Feuil3 With .Range("A1:A20000") .Formula = "=Row()" .Value = .Value End With End With End Sub '-----------------------------------
L'utilisation des formules est beaucoup plus rapide qu'une boucle lorsque la chose est possible.
Tu as compris l'essentiel. Il y a beaucoup de façon d'écrire du code. L'essentiel c'est que tu sois à l'aise avec la manière dont tu procèdes. Avec le temps, tu amélioreras sûrement l'efficacité de ton code.
Voici une procédure qui extrait toutes les données des colonnes de la feuil1.
En colonne A, comme tu l'as mentionné, il n'est pas supposé d'avoir de doublon.
S'il y a des cellules vides dans la feuil1, en Feuil3, cela affichera 0 . J'ai ajouté 2 lignes de codes qui les suppriment.
Selon les besoins spécifiques, on peut adapter la formule, ce n'est qu'un exemple!
'----------------------------------------------- Sub Test()
Dim Rg As Range, Rg1 As Range Dim DerCol As Integer, Nb As Long, DerLig As Long With Feuil1 DerCol = .Cells.Find(What:="*", _ LookIn:=xlValues, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious).Column DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row T = .Range(.Cells(2, 1), .Cells(DerLig, DerCol)) Set Rg = .Range("A2:A" & DerLig) 'Création de 2 plages nommées pour la formule Rg.Name = "MData" .Range(.Cells(2, 1), .Cells(DerLig, DerCol)).Name = "Plg" End With
With Feuil2 Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row) End With
Application.EnableEvents = False Application.ScreenUpdating = False With Feuil3 With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With 'Nb = Nombre de ligne en Feuil3 Nb = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row - 1 With .Range("B2") .Formula = "=INDEX(plg,MATCH($A2,Mdata,0),COLUMN())" .Resize(, Range("plg").Columns.Count - 1).FillRight With .Resize(Nb, Range("plg").Columns.Count - 1) .FillDown .Value = .Value End With End With 'Suppression des 0 représentant les cellules vides de 'la feuil1 si nécessaire With .UsedRange .Replace 0, "" End With End With 'Suppression des 2 plages nommées ThisWorkbook.Names("Plg").Delete ThisWorkbook.Names("Mdata").Delete Application.EnableEvents = True Application.ScreenUpdating = True
End Sub '-----------------------------------------------
MichD --------------------------------------------------------------- "khinoa" a écrit dans le message de groupe de discussion : 50234437$0$2352$
Bonjour,
J'ai testé tout tes codes, le plus rapide et de loin, comme tu le savais, est celui-ci :
Sub test3()
Dim Rg As Range, Rg1 As Range
Sheets(3).Cells.ClearContents 'Quoique que dans l'immédiat Columns("a").ClearContents suffisait voire Columns("a").Delete
With Sheets(2) Set Rg = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(1) Set Rg1 = .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) End With
With Sheets(3) With .Range("A2") .Formula = "=IF(COUNTIF(" & Rg1.Parent.Name & "!" & _ Rg1.Address & "," & Rg.Parent.Name & _ "!A2)=0," & Rg.Parent.Name & "!A2,"""")" With .Resize(Rg.Rows.Count) .FillDown .Value = .Value .Sort Key1:=.Item(1, 1), order1:=xlAscending End With End With End With
Application.EnableEvents = True Sheets(3).Select End Sub
Par contre je ne comprenais pas comment fonctionnait le code et pourquoi il était si rapide. En désactivant la partie With. Resize, j'ai vu la formule inscrite en cellule A2.
=SI(NB.SI(Feuil1!$A$2:$A$17000;2!FeuilA2)=0;Feuil2!A2;""). J'en ai déduis le fonctionnement du Parent. Name qui n'est sans doute pas obligatoire.
J'ai compris que le second With avec Resize et FillDown recopiait la formule dans chaque cellule et affichait les valeurs manquantes à l'endroit où elles manquent. Je pensais que l'utilisation d'une formule retardait le code, je vois qu'il n'en est rien. Puis une recopie de la formule le nombre de lignes nécessaire, .Value = .Value pour transformer la formule en son résultat et un tri pour afficher le résultat en haut de la feuille. Je comprends un peu mieux. Ai-je tout compris ? Les With sont-ils obligatoires ? Ne pourrait-on pas écrire le code sans les With ? Le .Item pourrait-il être vraisemblablement remplacé par Range("a2") ?
Le Parent. Name peut se remplacer par Rg1 tout court si Set = Rg1 .Range("A2:A" & .Cells(.Rows.Count, 1).End(3).Row) devient Set = Sheets(1).Range("A2:A" & Sheets(1).Cells(.Rows.Count, 1).End(3).Row), n'est-ce pas ?
En tout cas, merci déjà pour ton aide précieuse. Ce code a retrouvé 5 valeurs manquantes adjacentes en 6"40 (6 secondes) sur mon PC qui n'est pas une bête de course, suffisant pour de la bureautique basique. J'ai été époustouflé par la précision du code et sa rapidité. Ce sont des instructions (Set, Resize, la formule) que je connaissais séparément mais je ne pensais pas qu'en les combinant il était possible de parvenir à ce résultat. En fait l'élément déterminant était l'usage de la formule. Encore merci.
khinoa
PS : Ce matin j'ai travaillé sur un modèle vierge dans lequel avec une boucle j'ai inséré en colonne A de la feuille 1 un numéro d'ordre jusqu'à 17000 puis copie de la colonne A en feuille 2, suppression de quelques valeurs en feuille 1 et test.
"MichD" a écrit dans le message de groupe de discussion : jvuht8$cig$
Celle-ci ne transfère que les valeurs de la colonne A de la feuil1 vers la feuil3.
As-tu des doublons dans la colonne A de la feuil1 ?
