autofilter vba lent sur +100 000 lignes

[aurel2chien]

Bonjour à vous tous

J'utilise sous vba la méthode autofilter sur une colonne avec une valeur que je recherche sur plus de 100 000 lignes.
ensuite je fais un copier coller des lignes retournée.

Cependant je trouve que la macro est très très lente lors de l'execution du autofilter.

Existe-t-il une méthode plus rapide pour filter et copier coller les données quand le nombre de cellules est conséquent?

Merci pour votre aide.
belle fin de journée

 

[Softmama]

Re : autofilter vba lent sur +100 000 lignes

Bonjour,

il serait intéressant de voir l'architecture de tes données et le code exact utilisé, on serait plus à même de te venir en aide.

Pour info, sur mon ordinateur, cette boucle qui recherche 10 valeurs parmi 65536 dans la colonne A à base de Autofilter prend 1,5 secondes :

Private Sub CommandButton1_Click()
Dim A As Range
y = Timer
Application.ScreenUpdating = False
Range("A1:A65535").AutoFilter
For t = 1 To L
x = AleaMemo(t)
    ActiveSheet.Range("$A$1:$A$65535").AutoFilter Field:=1, Criteria1:=x
    Set A = ActiveSheet.Range("_FilterDataBase").SpecialCells(xlCellTypeVisible)
Next t
Range("A1:A65535").AutoFilter
Application.ScreenUpdating = True
Z = Timer
temps = Int((Z - y) * 1000) / 1000
TextBox1 = temps & "(Lig." & A.Areas(2).Row & ")"
End Sub

Sur ce même ordinateur, une recherche de 1000 valeurs (!) par Tableaux + Dichotomie demande 0,03 secondes :

Private Sub CommandButton2_Click()
y = Timer
tablo = Range("A1:A65536")
For t = 1 To 1000
x = AleaMemo(t)
deb = 0: fin = 65536
Do
    If deb > fin Then Exit Do
    pas = Int((deb + fin) / 2)
    If x = tablo(pas, 1) Then Exit Do
    If x < tablo(pas, 1) Then fin = pas - 1
    If x > tablo(pas, 1) Then deb = pas + 1
Loop
Next t
Z = Timer
temps = Int((Z - y) * 1000) / 1000
TextBox2 = temps & "(Lig." & pas & ")"
End Sub

A mettre donc dans un USF avec 2 boutons et 2 TextBox.

Je ne connais pas de méthode de recherches de valeurs plus rapide, mais cette technique telle que présentée ici, s'adapte à des données rangées en ordre croissant dans 1 seule colonne, sur 1 feuille de calcul. Je ne sais si c'est ton cas.
A te relire donc.

Edit: en amont, on a défini une variable AleaMemo() qui prend toutes les valeurs qui seront recherchées et on remplit la colonne A de valeurs uniques aléatoires.

 

[aurel2chien]

Re : autofilter vba lent sur +100 000 lignes

Merci beaucoup pour cette réponse si rapide.

J'ai une feuille qui contient 18 colonnes et plus de 100 000 lignes.
Je fais un filtre das une colonne, ensuite je copie et colle le résultat du filtre dans une autre feuille.
Je lance ma macro depuis une feuille menu qui est différent

Sheets("Conso_TR_Temp").Activate
ActiveSheet.Columns("A:R").AutoFilter Field:=2, Criteria1:="All"

Sheets("Conso_TR_Temp").Select
Range("A2:R" & Range("A500000").End(xlUp).Row + 1).Select
Selection.Copy
Sheets("Consolidated TR").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

(le application.screenupdating est mis à false en début de code tout comme le cakcul en mode manuel.

Encore merci

 

[aurel2chien]

Re : autofilter vba lent sur +100 000 lignes

J'ai les même problèmes de lenteur quand je fais:

With Sheets("Conso_TR_Temp")
If .AutoFilterMode And .FilterMode Then
.ShowAllData
End If
End With

 

[Softmama]

Re : autofilter vba lent sur +100 000 lignes

Re,

Le problème de vitesse est tout d'abord dû aux .Select qui ne servent à rien d'autre que précisément ralentir ta macro. Tu peux très bien t'en passer en les remplaçant par exemple :

Sheets("Conso_TR_Temp").Range("A2:R" & Range("A500000").End(xlUp).Row + 1).Copy 
Sheets("Consolidated TR").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[mromain]

Re : autofilter vba lent sur +100 000 lignes

Bonjour aurel2chien, Softmama, le forum 🙂

Une autre solution (performante et sans VBA) est MS-Query. Cet outil permet de faire des filtres très poussés.
Il exige cependant de suivre quelques règles dans la gestion des données :

• Ne pas utiliser de cellules fusionnées ;
• N'avoir qu'une seule ligne d'entêtes ;
• Que la ligne d'entête soit la première ligne de la feuille.

A la lecture de ton code, ça a l'air d'être le cas 🙂

A+
Miki

 

[aurel2chien]

Re : autofilter vba lent sur +100 000 lignes

Merci pour vos réponses.

J'ai fait un peu de nettoyage (plus de .select) et j'ai toujours le problème de lenteur.
Mes données ne sont pas sous forme de tableau.. j'ai peur que vu la taille (18*100 000) si je veuille transformer mes données sous forme de tableau ne ralentisse ,et ne surcharge pas trop le fichier.

 

[aurel2chien]

Re : autofilter vba lent sur +100 000 lignes

coucou

j'ai essayé le MS-Query et c'est toujours aussi lent.. je ne pense pas adopter cette solution.

est-ce que vous avez une autre idée pour faire du autofilter plus rapide?

Merci
belle fin de journée

 

[mromain]

Re : autofilter vba lent sur +100 000 lignes

Bonjour aurel2chien, le forum,

Si malgré le fait que tu désactive le rafraichissement de l'écran et le mode de calcul automatique, ta macro reste toujours lente, je pense que le problème est plus grave, et qu'il faudra donc que tu nous le fournisses (sans données confidentielles) afin d'y jeter un œil de plus près 🙂.
Le fait que MS-Query soit lent me fait arriver à la même conclusion...

Par pure curiosité, quelle taille fait ton fichier ???

A+
Miki