Supprimer des lignes sous condition

[boby]

Bonjour à tous,

je suis le nouveau de la classe

j'ai un fichier dont je souhaiterai supprimer quelques lignes sous conditions😡 :

- colonne H : supprimer toutes les lignes dont les codes ISIN ne commençant pas par "AT" - "DK" - "FI" - "GB" - "IE" - "JP" - "NL" - "PT" - "XS0"
ps: (0=zero)
😕
- colonne W: supprimer toutes les lignes dont les cellules ne contiennent pas "UNM" ou "ICMO" conserver les cellules vides
😱
- trier par ordre croissant les colonnes N puis O
🙄
- sauter trois lignes entières quand dans la colonnes N on passe de "C" à "E" et de "E" à "X"

- mise en page:
uniformiser les lignes et les colonnes avec un zoom à 82%, des cellules centrées ajustée, les colonnes J K et L en separateur de milliers, et une couleur de remplissage blanche puis fenetres/reorganiser/horizontal

voici un echantillon du fichier

merci d'avance pour votre aide

 

[Jacques87]

Re : Supprimer des lignes sous condition

Bonjour Boby,

Voici une macro qui répond à ta première question

Sub nettoyer
Dim I As Integer
Dim code As String, debut1 As String, debut2 As String
For I = 2 To Range("A65536").End(xlUp).Row
code = Range("H" & I).Value
debut1 = Left(code, 2)
debut2 = Left(code, 3)
If debut1 <> "AT" Or debut1 <> "DK" Or debut1 <> "FI" Or debut1 <> "GB" Or debut1 <> "JP" Or debut1 <> "IE" Or debut1 <> "NL" Or debut1 <> "PT" Or debut2 <> "XSO" Then
Rows(I).Delete Shift:=xlUp
End If
Next I
End Sub

Pour la suite c'est un peu plus compliqué car il faut analyser des chaînes de caractères de façon plus complexe.

A+

P.S. : J'ai fait une bourde dans mon test. Il faut changer les "Or" en "And", mais de plus je me heurte à une difficulté du fait que les troncatures (debut1 et debut2) sont de taille différente.
Je regarderai cela ce soir

 

[boby]

Re : Supprimer des lignes sous condition

Merci Jacques de t'etre penché sur mon cas

je viens de tester ta macro
elle ne fait pas ce que je lui demande
et pire si je la relance elle supprime meme les lignes que je lui demande de conserver (pour la premiere partie)

la partie suivante devrait normalement se traiter de la meme maniere puisque le principe est le meme, seul le caractere de la cellule change

j'avais essayer avec l'assistant macro en utilisant le filtre automatique et supprimant par bloc les ligne que je ne voulais pas conserver

j'obtenais de meilleurs resultats mais des lignes passaient tout de meme au travers du filet

si tu as des question n'hesites pas
et encore merci

 

[Jacques87]

Re : Supprimer des lignes sous condition

Boby, je me suis rendu compte de mon erreur (cf le post précédent)

en te limitant à :

Sub nettoyer
Dim I As Integer
Dim code As String, debut1 As String, debut2 As String
For I = 2 To Range("A65536").End(xlUp).Row
code = Range("H" & I).Value
debut1 = Left(code, 2)
If debut1 <> "AT" And debut1 <> "DK" and debut1 <> "FI" And debut1 <> "GB" And debut1 <> "JP" And debut1 <> "IE" And debut1 <> "NL" And debut1 <> "PT" Then
Rows(I).Delete Shift:=xlUp
End If
Next I
End Sub

Tu as un début de réponse

A ce soir

 

[boby]

Re : Supprimer des lignes sous condition

salut Jacques,

meme reponse que precedemment, je ne sais pas ou ça cloche car je ne suis pas un expert dutout
je n'arrive pas a obtenir le resultat escompté:

voici une vue de ce que j'avais il n'y a pas si longtemps:

Sub UNMATCHED()
'
' UNMATCHED Macro
' Macro enregistrée le 09/03/2006 par moi
'

'
Cells.Select
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
End With
Range("I5").Select
Cells.Select
Cells.EntireColumn.AutoFit
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("H5").Select
Selection.Sort Key1:=Range("H5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Rows("1:1").Select
Selection.Font.Bold = True
Range("D10").Select
Selection.AutoFilter
ActiveWindow.LargeScroll ToRight:=1
Columns("J:L").Select
Selection.Style = "Comma"
Range("O33").Select
ActiveWindow.SmallScroll ToRight:=11
Selection.AutoFilter Field:=23, Criteria1:="<>*UN*", Operator:=xlAnd
Rows("2:3206").Select
Range("T2").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-12
Selection.AutoFilter Field:=23
Selection.AutoFilter Field:=22, Criteria1:="=PAID", Operator:=xlOr, _
Criteria2:="=PPAID"
Rows("104:961").Select
Range("T104").Activate
Selection.Delete Shift:=xlUp
Range("W108").Select
Selection.AutoFilter Field:=22
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.SmallScroll ToRight:=-4
Selection.AutoFilter Field:=8, Criteria1:="=BE*", Operator:=xlOr, _
Criteria2:="=DE*"
Rows("4:1535").Select
Range("C4").Activate
Selection.Delete Shift:=xlUp
Range("H49").Select
Selection.AutoFilter Field:=8, Criteria1:="=FR*", Operator:=xlOr, _
Criteria2:="=GB*"
Rows("5:1452").Select
Range("C5").Activate
Selection.Delete Shift:=xlUp
Range("I30").Select
Selection.AutoFilter Field:=8, Criteria1:="=ES*", Operator:=xlOr, _
Criteria2:="=IT*"
Rows("4:1648").Select
Range("C4").Activate
Selection.Delete Shift:=xlUp
Range("H30").Select
Selection.AutoFilter Field:=8, Criteria1:="=NZ*", Operator:=xlOr, _
Criteria2:="=US*"
Rows("15:1724").Select
Range("C15").Activate
Selection.Delete Shift:=xlUp
Range("H28").Select
Selection.AutoFilter Field:=8, Criteria1:="=CA*", Operator:=xlOr, _
Criteria2:="=GR*"
Rows("28:1737").Select
Range("C28").Activate
Selection.Delete Shift:=xlUp
Range("H1").Select
Selection.AutoFilter Field:=8
Cells.Select
Range("C1").Activate
Cells.EntireColumn.AutoFit
Range("F10").Select
ActiveWindow.SmallScroll ToRight:=7
Range("O13").Select
Selection.Sort Key1:=Range("O13"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.SmallScroll ToRight:=-6
Columns("J:J").Select
Selection.EntireColumn.Hidden = True
Range("I14").Select
ActiveWindow.SmallScroll ToRight:=2
Rows("1:1").RowHeight = 38.25
Rows("1:1").Select
Range("H1").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = True
.MergeCells = False
End With
Range("I4").Select
Columns("M:M").ColumnWidth = 6.71
Columns("N:N").ColumnWidth = 6
Columns("O:O").ColumnWidth = 7.57
Rows("1:1").Select
Range("H1").Activate
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Range("K8").Select
ActiveWindow.SmallScroll ToRight:=0
End Sub

 

[porcinet82]

Re : Supprimer des lignes sous condition

Salut Boby, Mon cher Jacques,

Voici le code que t'aurais certainement proposé mon ami Jacques (a quleques chose près) si je lui en avait laissé le temps 😛

Sub nettoyer()
Dim i As Integer
Dim code As String, debut1 As String, debut2 As String
Application.ScreenUpdating = False
For i = Range("A65536").End(xlUp).Row To 2 Step -1
Select Case Left(Cells(i, 8).Value, 2)
Case "AT", "DK", "FI", "GB", "JP", "IE", "NL", "PT", "XS" & 0
Rows(i).Delete Shift:=xlUp
End Select
'colonne W: supprimer toutes les lignes dont les cellules ne contiennent pas "UNM" ou "ICMO" conserver les cellules vides
If IsEmpty(Cells(i, 24)) = False Then
If InStr(1, Cells(i, 24).Value, "UNM") = 0 Or InStr(1, Cells(i, 24).Value, "ICMO") = 0 Then
Rows(i).Delete Shift:=xlUp
End If
End If
Next i
'trier par ordre croissant les colonnes N puis O
Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Key2:=Range("O2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
'sauter trois lignes entières quand dans la colonnes N on passe de "C" à "E" et de "E" à "X"
For i = 2 To Range("A65536").End(xlUp).Row
If Cells(i, 14).Value = "C" And Cells(i + 1, 14).Value = "E" Then Rows(i + 1 & ":" & i + 3).Insert Shift:=xlDown
If Cells(i, 14).Value = "E" And Cells(i + 1, 14).Value = "K" Then Rows(i + 1 & ":" & i + 3).Insert Shift:=xlDown
Next i
ActiveWindow.Zoom = 82
With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
End With
Columns("J:L").NumberFormat = "#,##0.00 _€"
Windows.Arrange ArrangeStyle:=xlHorizontal
Application.ScreenUpdating = True
End Sub

Désolé Jacques, mais chez moi il pleut, je n'ai donc rien a faire... Mais au plaisir de te croiser,

@+

PS : Une petite remarque pour mon ami Jacques, lorsque l'on supprime des lignes, il vaut mieux prendre un pas inversé 😉

 

[boby]

Re : Supprimer des lignes sous condition

Salut Porcinet,

je pense qu'on s'approche du but
ta macro fait un peu l'inverse de ce que je veux
elle supprime tout ce que je veux garder
et garde tout ce que je veux supprimer

elle garde une partie des "XS0" (0=zero)

pour ce qui est de la mise en forme elle est au poil si ce n'est que j'aimerai bien faire tenir le fichier sur une page en largeur, et la largeur des colonne a besoin d'etre revue dans certaine colonnes

colonne W elle ne me supprime pas les ligne dont les cellules ne contiennent pas UNM ou ICMO ou vide

je pense que tu es sur la bonne voix
encore merci
est ce que tu peux corriger ces points
ce serait super

 

[Jacques87]

Re : Supprimer des lignes sous condition

Re Boby,

J'ai pris un peu plus de temps pour réfléchier et trouver une solution pour la première partie de ta question, en voici le résultat :

Sub nettoyer
Dim I As Integer, K As Integer
Dim code As String, code2 As String

For I = Range("H65536").End(xlUp).Row To 2 Step -1
code = Left(Range("H" & I).Value, 2)
If code <> "AT" And code <> "DK" And code <> "FI" And code <> "GB" And code <> "JP" And code <> "IE" And code <> "NL" And code <> "PT" And code <> "XS" Then
Rows(I).Delete Shift:=xlUp
End If
Next I
For K = Range("H65536").End(xlUp).Row To 2 Step -1
code2 = Left(Range("H" & K).Value, 3)
If Left(code2, 2) = "XS" And Right(code2, 1) <> "0" Then
Rows(K).Delete Shift:=xlUp
End If
Next K

End Sub

j'ai conservé mon idée première en l'adaptant à tes besoins

Pour la suite, je verrai tout à l'heure, car en ce moment je fais 3 choses en mlême temps.


Mon cher Romain, bonjour

On m'avait toujours dit de ne pas courrir 2 ou 3 lièvres en même temps.
Ce vieil adage s'est encore avéré pertinent.
Comment tondre la pelouse, préparer le repas et essayer de développer en même temps. Forcément on fait une chose de travers.

En ce qui concerne ta remarque, "lorsque l'on supprime des lignes, il vaut mieux prendre un pas inversé ", il est évident que je connais cette astuce pour l'avoir utilisée maintes fois, mais dans la précipitation ....

Amicalement et au prochain Fil

 

[Jacques87]

Re : Supprimer des lignes sous condition

Salut Boby,

Voila, après le repas, j'ai repris tranquillement et posément ton fichier.
J'espère que tu attendais ce résultat.
En ce qui concerne la mise en forme, j'ai repris le code de Romain

Bonne soirée

la macro s'appelle "nettoyer"

 

[boby]

Re : Supprimer des lignes sous condition

Bonjour à tous,

je vous remercie infiniement pour le mal que vous vous donnez
(comme j'aimerai pouvoir programmer comme vous le faites)

je viens de jetter un coup d'oeil à ce que vous m'avez envoyé
moi qui suis un novice de chez novice en matière de programmation, je dois avouer que la maniere de programmer de Porcinet m'est plus facile à comprendre et elle est plus rapide.

cependant il subsiste quelques imperfection:

- j'ai inversé la premiere partie en prenant en compte ce que je voulais supprimer et non ce que je voulais conserver comme debut de code ISIN, ca marche plutot bient sauf pour les codes qui commancent par XSDUMMY, je n'arrive pas à tous les supprimer, il en subsistes quelques uns

- l'ajsutement ou l'uniformisation les lignes et les colonnes ne fonctionne pas partout (je pense qu'il doit etre fait à la fin)

- je souhaiterai avoir la premiere ligne en gras, largeur de ligne 30, couleur remplissage Turquoise clair, ombre portee style 6

- j'aurai souhaité sauter egalement trois lignes entre la valeur X et la cellule vide ou bien entre la valeur E et la cellule vide au cas ou il n'y aurai pas de X

- la partie qui concerne la colonne W ne fonctionne pas: colonne W: supprimer toutes les lignes dont les cellules ne contiennent pas "UNM" ou "ICMO" ou "FAIL" et conserver les cellules vides

j'ai essayer de bricoler un peu ta macro, je n'ai pas reussi partout, voila ce que j'ai obtenu:

Sub nettoyage()
Dim I As Integer
Dim code As String, debut1 As String, debut2 As String
Application.ScreenUpdating = False
For I = Range("A65536").End(xlUp).Row To 2 Step -1
Select Case Left(Cells(I, 8).Value, 2)
Case "BE", "DE", "ES", "FR", "HU", "IT", "NO", "SE", "US", "XSDUMMY"
Rows(I).Delete Shift:=xlUp
End Select
'colonne W: supprimer toutes les lignes dont les cellules ne contiennent pas "UNM" ou "ICMO" conserver les cellules vides
If IsEmpty(Cells(I, 24)) = False Then
If InStr(1, Cells(I, 24).Value, "UNM") = 0 Or InStr(1, Cells(I, 24).Value, "ICMO") = 0 Or InStr(1, Cells(I, 24).Value, "FAIL") = 0 Then
Rows(I).Delete Shift:=xlUp
End If
End If
Next I
'trier par ordre croissant les colonnes N puis O
Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Key2:=Range("O2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
'sauter trois lignes entières quand dans la colonnes N on passe de "C" à "E" et de "E" à "X"
For I = 2 To Range("A65536").End(xlUp).Row
If Cells(I, 14).Value = "C" And Cells(I + 1, 14).Value = "E" Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
If Cells(I, 14).Value = "E" And Cells(I + 1, 14).Value = "X" Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
If Cells(I, 14).Value = "E" And IsEmpty(Cells(I, 14)) = True Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
Next I
ActiveWindow.Zoom = 82
With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
End With
Columns("J:L").NumberFormat = "#,##0.00 _€"
Windows.Arrange ArrangeStyle:=xlHorizontal
Application.ScreenUpdating = True
End Sub

 

[boby]

Re : Supprimer des lignes sous condition

rebonjour,

je bricole je bricole
que voulez vous quand on est nul comme moi en programmation
on fait beaucoup appel à l'assistant
je vien de regler le probleme des de l'ajustement ligne et colonne en ajoutant ça à la fin

ActiveWindow.Zoom = 80
Cells.Select
Cells.EntireColumn.AutoFit
Range("C5").Select
End Sub

pas folle la guepe

mais bon il reste tous les autres problemes
please help

 

[Jacques87]

Re : Supprimer des lignes sous condition

Bonjour,

certes ma façon de procéder est un peu plus complexe que celle de Romain. Ma méthode consiste à premièrement (gràce à une boucle) éliminer toutes les lignes répondant au critère que tu as donné lorsque ce dernier est constitué de 2 lettres. Ensuite je fais un deuxième balayage pour éliminer celles qui correspondes au critère à 3 lettres.

Quant à la colonne "W" je recherche dans cfhaque cellule si premièrement la chaîne "UNM" existe, si c'est le cas je repère la position de son premier caractère "U"; Et je recommence la même chose avec la chaîne "ICMO".

Quant à la suite comme je te l'ai précisé j'ai repris le code de Romain

Bon courage

 

[boby]

Re : Supprimer des lignes sous condition

merci beaucoup Jacques

je vines de regler le probleme des XSDUMMY qui subsistaient
en faisant ça

Select Case Left(Cells(I, 8).Value, 7)
Case "XSDUMMY"
Rows(I).Delete Shift:=xlUp
End Select

je suis assez content de moi

je continue à bricoler, n'hesitez pas à m'aider
votre aide m'est précieuse

 

[boby]

Re : Supprimer des lignes sous condition

bonjour,


- j'aurai souhaité sauter egalement trois lignes entre la valeur X et la cellule vide ou bien entre la valeur E et la cellule vide au cas ou il n'y aurai pas de X


mais il y a une erreur dans ma formule, pouvez vous m'aider à la detecter et la résoudre ?

merci

For I = 2 To Range("A65536").End(xlUp).Row
If Cells(I, 14).Value = "C" And Cells(I + 1, 14).Value = "E" Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
If Cells(I, 14).Value = "E" And Cells(I + 1, 14).Value = "X" Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
If Cells(I, 14).Value = "E" And IsEmpty(Cells(I, 14)) = True Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown

 

[boby]

Re : Supprimer des lignes sous condition

bonjour,

je suis toujours en train de bricolage
ça reste toujours du bricolage mais ça marche

je viens de regler le probleme de la colonne W
il me reste toujours les autres problèmes à régler

voici la tete de ma nouvelle macro


Sub cleaner()
Dim I As Integer
Dim code As String, debut1 As String, debut2 As String
Application.ScreenUpdating = False
For I = Range("A65536").End(xlUp).Row To 2 Step -1
Select Case Left(Cells(I, 8).Value, 2)
Case "BE", "DE", "ES", "FR", "HU", "IT", "NO", "SE", "US"
Rows(I).Delete Shift:=xlUp
End Select
Select Case Left(Cells(I, 8).Value, 7)
Case "XSDUMMY"
Rows(I).Delete Shift:=xlUp
End Select
'colonne W: supprimer toutes les lignes dont les cellules ne contiennent pas "UNM" ou "ICMO" conserver les cellules vides
Select Case Left(Cells(I, 23).Value, 25)
Case "E - DONE", "E - INT - FUT", "E - INT - FUT - EXT - MAT", "Filtered", "Stamina - Filtered", "E - GENR"
Rows(I).Delete Shift:=xlUp
End Select
Next I
'trier par ordre croissant les colonnes N puis O
Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Key2:=Range("O2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
'sauter trois lignes entières quand dans la colonnes N on passe de "C" à "E" et de "E" à "X"
For I = 2 To Range("A65536").End(xlUp).Row
If Cells(I, 14).Value = "C" And Cells(I + 1, 14).Value = "E" Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
If Cells(I, 14).Value = "E" And Cells(I + 1, 14).Value = "X" Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
If Cells(I, 14).Value = "E" And IsEmpty(Cells(I, 14)) = True Then Rows(I + 1 & ":" & I + 3).Insert Shift:=xlDown
Next I
Columns("J:L").NumberFormat = "#,##0.00 _€"
Windows.Arrange ArrangeStyle:=xlHorizontal
Application.ScreenUpdating = True
With Cells
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 2
End With
ActiveWindow.Zoom = 80
Cells.Select
Cells.EntireColumn.AutoFit
Range("C5").Select
End Sub


voici ce qui me reste à faire

- je souhaiterai avoir la premiere ligne en gras, largeur de ligne 30, couleur remplissage Turquoise clair, ombre portee style 6

- j'aurai souhaité sauter egalement trois lignes entre la valeur X et la cellule vide ou bien entre la valeur E et la cellule vide au cas ou il n'y aurai pas de X


je pense à mettre des mises en forme conditionnelles
avez vous une astuce
la mise en forme sera du gense
si la cellule = B mettre en gras couleur bleu nuit
si la cellule = S mettre en gras couleur Rouge
si la cellule = T mettre en gras couleur violet

c'est grace à vous si j'ai pu faire tout ça
j'ai beaucoup appris en deux jours

encore merci de votre aide