Discussion :

[stephb084]

Bonjour à tous,

J'ai crée un code avec plusieurs USF qui m'affiche des infos a partir d'une BDD. Tous fonctionne trés bien. Mon seul soucis c'est que les infos sont récuperées sur une BDD de plus de 46000 lignes et donc cela prend du temps. J'imagine que le code que j'ai écris doit pouvoir être modifié, optimisé de maniére a fonctionner plus rapidement.

Je souhaite avoir des conseils de personnes experimentées pour m'orienter sur la maniére de pouvoir reduire le temps de fonctionnment des macro.


Je transmet en PJ le fichier excel. Les données de bases etant confidentielles, je l'ai ai donc remplacer. (Le classeur n'est plus trop comprehensible du coup lol). J'ai du reduire la BDD a 20000 lignes pour pouvoir le transferer sur le forum. Mais le fichier tourne sur plus de 46000.

Je ne souhaite pas de solution "clé en main" comme on a pu me le reprocher sur d'autre demande d'aide. Je souhaite des conseils et des orientations pour avancer.


Merci d'avance pour votre aide.

[anasecu]

Bonjour,

Je ne souhaite pas de solution "clé en main" comme on a pu me le reprocher sur d'autre demande d'aide. Je souhaite des conseils et des orientations pour avancer.

Bon j'ai survolé ton code et je pense que pour l’accélérer, il faudrait passer par des tableaux mémoire, au lieu de

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
    For Each Cell In Sheets("BDD").Range("C1:C" & i)
        'Stocke les données dans une collection
        '(La collection n'accepte que des données uniques et permet donc
        ' de filtrer facilement les doublons).
        If ListBox1.Text = Cell.Offset(0, -1) And Cell.Offset(0, 1) = "En cours" And Cell.Offset(0, 2) < Date Then
         Unique.Add Cell, CStr(Cell) 
End If
Next Cell

par exemple mettre un code dans ce style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
    tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count)
    For i = 1 To UBound(tbd)
        If ListBox1.Text = tbd(i, 2) And tbd(i, 4) = "En cours" And tbd(i, 5) < Date Then
            Unique.Add Cell, CStr(Cell)
        End If
    Next i

Ainsi tu supprimes les adressages feuille qui sont bien plus longs

[patricktoulon]

bonsoir anasecu

relis ce que tu a proposé Svp!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count)
    For i = 1 To UBound(tbd)
        If ListBox1.Text = tbd(i, 2) And tbd(i, 4) = "En cours" And tbd(i, 5) < Date Then
            Unique.Add Cell, CStr(Cell)
        End If
    Next i

dans ta boucle c'est quoi "cell" !!!!!??????

et ceci est dangereux et faux 'erreur 438"

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count)

et meme suposont que ca marche et que je veux la plage c1:d10 :10 etant la derniere ligne utilisée sur une feuille rempli jusque colonne F par exemple

voyons voir ta ligne devrait me donner

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Sheets("BDD").UsedRange.Cell(1, 1).Resize(10, 6) on est loin de la plage C1:Cx

tu vois le chmilblick
resizer avec le columns.count oui!! sauf !!! si la plage ne commence pas en colonne A

[anasecu]

Bonsoir Patrick,

Tu as raison j'ai recopié la ligne qui aurait dû être

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

            Unique.Add tbd(i, 3), CStr(tbd(i, 3))

on est loin de la plage C1:Cx

Oui mais là c'est tout à fait normal car il faut plus que la colonne C pour tester ! ;-)

Comme je n'avais pas testé (honte à moi) il manquait aussi un 's'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

    tbd = Sheets("BDD").Cells(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count).Value

Merci de ta rectification mais elle était partielle...

[patricktoulon]

reste que ceci n'est pas bon pour 2 raisons

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

tbd = Sheets("BDD").UsedRange.Cell(1, 1).Resize(Cells(Rows.Count, 3).End(xlUp).Row, Sheets("BDD").UsedRange.Columns.Count)

veux tu savoir pourquoi?

[patricktoulon]

re
tien c'est pour toi annasecu
c'est ce qui est voulu je crois il me semble
rempli quelque ligne sur un sheets("BDD") en metant au moins une cellule pleine en colonne A et va jusqu'a a F ou plus copmme tu veux

apres teste ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub testx1()
    With Sheets("BDD")
        'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count)
     Set tbd = .Range([c3], .Range([A3], .UsedRange)).Columns("C:C")
    MsgBox tbd.Address
    End With
End Sub

ou ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub testx2()
    With Sheets("BDD")
        'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count)
     Set tbd = .Range([c3], .UsedRange).Columns("C:C")
    MsgBox tbd.Address
    End With
End Sub

ou ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub testx3()
    With Sheets("BDD")
        'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count)
     Set tbd = .Range([c3], .Range([A3], .UsedRange)).Columns("C:C")
    MsgBox tbd.Address
    End With
End Sub

ou ca

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub testx4()
    With Sheets("BDD")
        'Set tbd = .UsedRange.Cell(1, 1).Resize(.Cells(Rows.Count, 3).End(xlUp).Row, .UsedRange.Columns.Count)
     Set tbd = .Range([A3], .UsedRange).Columns("C:C")
    MsgBox tbd.Address
    End With
End Sub

maintenant vide la colonne A et reteste surprise surprise

le dernier exemple etant explicite de part l'address de colonne en string (et pourtant!!... comme dit la chanson)

perso le cells(rows.count,y) .end(xlup) reste le plus sur

et pour en revenir a steph comme elle boucle sur les valeurs sans selectionner c'est pas la qu'elle va gagner en velocité

[Pierre Fauconnier]

Salut.

Il y a plusieurs solutions. Les filtres avancés proposés par Patrick () en sont une, probablement très rapide.

Question que je me pose: Pourquoi remplir une collection puis vider la collection dans la listbox? Pourquoi ne pas directement alimenter la listbox au moment du test?

Autre solution, qui dépend de ta version d'Excel (il faut au moins la 2010). Utiliser PowerQuery qui ferait un peu office de filtre avancé mais qui ne nécessite comme programmation qu'une actualisation de la table produite par PowerQuery.

Autre chose: Savoir ce que tu souhaites réaliser pour te proposer, peut-être, une meilleure conception de ton classeur et de ton code.