Completer une macro enregistree - debutant

[urbanito1]

Bonjour à la communauté,

j'ai une petite macro enregistrée à laquelle je voudrais rajouter un petit complément.

deux fichiers en annex

le premier liste initiale
le second, ce que j'ai déjà fait et le complément souhaité

merci à vous

Urbanito1

 

[job75]

Re : Completer une macro enregistree - debutant

Re,

Une analyse sur la durée d'exécution de la version (2) permet de voir qu'en fait c'est l'instruction :

.SpecialCells(xlCellTypeConstants, 3) = 1

qui prend du temps (elle remplace les constantes numériques ou de texte par 1).

A+

 

[urbanito1]

Re : Completer une macro enregistree - debutant

Bien vu job
assomé je suis
merci

 

[job75]

Re : Completer une macro enregistree - debutant

Re,

Eh bien maintenant voici la version (3), toujours avec la conversion de données :

Sub TraiterListe()
If [B1] Like "Matricule*" Then Exit Sub 'liste déjà traitée
Dim supp1, supp2, h&
supp1 = 5 'adapter la valeur à supprimer
supp2 = 200 'adapter la valeur à supprimer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next 'si les SpecialCells n'existent pas
h = Cells(Rows.Count, 1).End(xlUp).Row
[B:B].Insert 'insertion de 2 colonnes
[B:B].Insert
[A1].Resize(h).TextToColumns [A1], xlDelimited, Other:=True, OtherChar:="("
With [B1].Resize(h)
  .TextToColumns [B1], xlDelimited, Other:=True, OtherChar:=")"
  .TextToColumns [B1], xlDelimited, Other:=True, OtherChar:=":"
  .FormulaR1C1 = "=1/AND(RC7<>" & supp1 & ",RC7<>" & supp2 & ")"
  'tri pour avoir les #DIV/0! en fin de liste (suppression plus rapide)
  [A1].Resize(h, Columns.Count).Sort [B1], xlAscending
  .SpecialCells(xlFormulas, 16).EntireRow.Delete
End With
[B:B].Delete
[A1] = "Nom Prénom"
[B1] = "Matricule Agent"
Columns("A:B").AutoFit 'ajustement automatique
End Sub

Elle entre une formule en colonne B qui permet de trier la valeur d'erreur #DIV/0.

C'est la version la plus rapide : 3,2 s sur 60001 lignes avec Excel 2003.

Juste un détail : la conversion de données laisse un espace à la fin des textes en colonne A...

A+

 

[job75]

Re : Completer une macro enregistree - debutant

Re,

Pour terminer, cette version (4) qui supprime les espaces inutiles en colonne A :

.FormulaR1C1 = "=TRIM(RC1)" 'SUPPRESPACE
 [A1].Resize(.Count) = .Value

Durée d'exécution à peine plus rapide que la version (1) => 3,5 s.

A+

 

[mapomme]

Re : Completer une macro enregistree - debutant

Bonjour à tous, à job75,

Si je ne me suis pas planté, une version qui chez moi est encore plus rapide. Je me demande si je n'ai pas fait une erreur grossière
J'arrive à 2s avec un peu moins de 30 000 lignes à supprimer et à 1,6s si aucune ligne à supprimer (pour 60 000 lignes de données).

Sub Mat()
Dim U, i, LigneMax, T1 As Double, xRg As Range
Sheets("resultat").Activate
Application.ScreenUpdating = False
T1 = Timer
If Range("B1") = "Matricule" Then
    MsgBox "Déjà traité"
    Exit Sub
End If
LigneMax = Cells(Rows.Count, "a").End(xlUp).Row
If LigneMax = 1 Then Exit Sub
Columns("B").Insert Shift:=xlToRight

U = Range(Cells(2, "f"), Cells(LigneMax, "f")).Value
For i = LBound(U, 1) To UBound(U, 1)
    If U(i, 1) = 200 Or U(i, 1) = 5 Then U(i, 1) = "" Else U(i, 1) = i
Next i
Range(Cells(2, "b"), Cells(LigneMax, "b")).Value = U
Range(Cells(2, "a"), Cells(LigneMax, "ae")).Sort key1:=Columns("b"), Header:=xlNo
Set xRg = Range(Cells(2, "b"), Cells(LigneMax, "b"))
Set xRg = xRg.Find(what:="", after:=xRg(1, 1), searchdirection:=xlNext)
If Not xRg Is Nothing Then Range(xRg, Cells(LigneMax, "b")).EntireRow.Delete
LigneMax = Cells(Rows.Count, "a").End(xlUp).Row
If LigneMax = 1 Then Exit Sub

U = Range(Cells(2, "a"), Cells(LigneMax, "b")).Value
For i = LBound(U, 1) To UBound(U, 1)
    U(i, 2) = Mid(U(i, 1), InStr(U(i, 1), ":") + 1, 99)
    U(i, 2) = Trim(Left(U(i, 2), InStr(U(i, 2), ")") - 1))
    U(i, 1) = Trim(Left(U(i, 1), InStr(U(i, 1), "(") - 1))
Next i
Range(Cells(2, "a"), Cells(LigneMax, "b")).Value = U
Range("A1") = "Nom": Range("B1") = "Matricule"
Application.ScreenUpdating = True
MsgBox (Timer - T1)
End Sub

NB: Bonjour job75
C'est bizarre! Si je ne mets aucun 5 ou 200 dans ma colonne E, ta procédure plante chez moi malgré le (ça devrait pas!):

Application.DisplayAlerts = False

 

[job75]

Re : Completer une macro enregistree - debutant

Bonjour mapomme

Effectivement ta macro est 2 fois plus rapide que ma version (1) par tableau.

Sur mon "gros" fichier de 60001 lignes => 1,8 s au lieu de 3,6 s.

Je remarque 2 choses :

- tu supprimes les lignes (5-200) avant de traiter la colonne A, et c'est plus logique

- tu travailles sur des tableaux de 1 ou 2 colonnes, alors que le mien en prend 7.

Ceci explique sans doute cela.

En tout cas bravo, ta macro est la meilleure

PS : sur mes fichiers je ne vois pas de problème si aucune valeurs 5 ou 200

A+

 

[mapomme]

Re : Completer une macro enregistree - debutant

Bonsoir job75,

Y'a pas de bravos qui tiennent. Je ne cherche pas la compétition, juste à bricoler avec Excel. Par contre, ce qui me chiffonne, c'est l'erreur qui se produit illogiquement chez moi. Je cherche mais pour l'instant je sèche...

 

[urbanito1]

Re : Completer une macro enregistree - debutant

bonjour à tous
je ne sais pas si ça peut vous aider mapomme
il y a une différence dans l'intitulé des colonnes entre les listes initiales
en ce qui me concerne j'ai assez de matière pour quelques jours
encore merci
urbanito1

 

[job75]

Re : Completer une macro enregistree - debutant

Re,

mapomme m'a amené à chercher pourquoi ma version (1) se trainait et j'ai trouvé.

Mon tableau tablo a 7 colonnes et je le restituais dans la feuille par :

[A1:G1].Resize(h) = tablo 'restitution

alors qu'il suffit de restituer les 3 premières colonnes :

[A1:C1].Resize(h) = tablo 'restitution 3 colonnes

La durée d'exécution passe ainsi de 3,6 s à 2,3 s

J'en ai profité, dans cette version (5), pour introduire une liste des valeurs à supprimer :

supp = "#5#200#" 'liste des valeurs à supprimer

utilisée dans la boucle avec :

If InStr(supp, "#" & tablo(i, 7) & "#") = 0 Then tablo(i, 3) = 1

A+

 

[urbanito1]

Re : Completer une macro enregistree - debutant

helo job, la communauté,

je viens de faire le test avec 92179 lignes : impeccable

j'ai fait aussi la suppression du code 200 uniquement : impeccable

le tableau que je reçois va s'aggrandir en fonction des mois pour arriver +- 150000 lignes en fin d'année

je ne vois pas de limitation au niveau des lignes dans le code( à moins que je ne me trompe)

je voudrais en semaine le compléter avec deux autres macros (au boulot), je vais d'abord essayer de le faire, à défaut, puis-je continuer sur le même fil ou dois-je ouvrir une nouvelle discussion?

en vous remerciant
urbanito1

 

[job75]

Re : Completer une macro enregistree - debutant

Re,

Le nombre de lignes est limité par la feuille Excel : 1048576 sur Excel 2007/2010...

Pour les problèmes que vous avez présentés ici je crois qu'on a fait à peu près le tour.

Il vaudra donc mieux ouvrir de nouvelles discussions si elles n'ont rien à voir avec ce fil.

A+

 

[mapomme]

Re : Completer une macro enregistree - debutant

Bonjour,

A priori les codes de job75 et le mien ne présentent pas de limitation de ligne.
Si les nouvelles questions ne concernent pas le PB actuel, vous pouvez commencer un ou des nouvelles discussions (une par PB). D'autres membres du forum s'y intéresseront plus facilement.

 

[urbanito1]

Re : Completer une macro enregistree - debutant

Merci bien
ce fut très enrichissant
urbanito1