Tester un cellule et action en fonction.

**shepard01** · 12/02/2020, 09h45

Bonjour,

J'aimerai le code qui permet de faire :
- Si une cellule X est vide ou égale à zéro ou renvoie l'erreur #REF! ou renvoie l'erreur #N/A alors supprimer la ligne (de cette cellule), sinon rien.

Merci,

**mat955** · 12/02/2020, 10h24

Bonjour,

Quelques pistes que tu devras adapter, mais qui devraient t'aider :

Code :

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

1
2
3
If Range("A1").value = "" Or Range("A1").value = 0 then
     Range("A1").EntireRow.Delete
End If

**shepard01** · 12/02/2020, 11h38

Merci,

Je souhaiterai faire cela sur un tableau défini par son nom "tab_1" en testant les cellules de la colonne A.
Au niveau du code ça ressemblerai à quoi ?

**Patrice740** · 12/02/2020, 13h38

Bonjour,

Il ne me semble pas normal qu'un tableau contienne des erreurs #Ref! mais ....

Essaies :

Code VBA :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Option Explicit
Sub test()
Dim t As ListObject
Dim v As Variant
Dim i As Long
  Set t = Worksheets("Feuil1").ListObjects("tab_1")  'adapter le nom de la feuille
  For i = t.ListColumns(1).DataBodyRange.Rows.Count To 1 Step -1
    v = t.ListColumns(1).DataBodyRange(i).Value
    If IsError(v) Then
      If v = CVErr(XlCVError.xlErrNA) Or v = CVErr(XlCVError.xlErrRef) Then
        t.ListRows(i).Delete
      End If
    Else
      If IsEmpty(v) Or v = 0 Then
        t.ListRows(i).Delete
      End If
    End If
  Next i
End Sub

EDIT : ce code répond strictement aux demandes :
1. Si une cellule X est vide ou égale à zéro ou renvoie l'erreur #REF! ou renvoie l'erreur #N/A alors supprimer la ligne
2. Je souhaiterai faire cela sur un tableau défini par son nom "tab_1" en testant les cellules de la colonne A
donc il ne supprime pas la ligne quand la cellule contient une chaine vide.

**shepard01** · 12/02/2020, 17h24

Envoyé par Patrice740

Il ne me semble pas normal qu'un tableau contienne des erreurs #Ref! mais ....

Effectivement, c'est complètement inutile car je ne suis pas sensé avoir ce code erreur(Présent dans ma feuille de tests seulement). A retirer.

Merci pour le code mais j'ai un soucis : "erreur d’exécution '9' : l'indice n'appartient pas à la sélection." sur la ligne

Code :

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

Set t = Worksheets("Sapo.").ListObjects("tab_1")  'adapter le nom de la feuille

J'ai aussi essayé avec le nom associé "Feuil14"

**Patrice740** · 12/02/2020, 17h36

Envoyé par shepard01

Effectivement, c'est complètement inutile car je ne suis pas sensé avoir ce code erreur(Présent dans ma feuille de tests seulement). A retirer.

Merci pour le code mais j'ai un soucis : "erreur d’exécution '9' : l'indice n'appartient pas à la sélection." sur la ligne

Code :

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

Set t = Worksheets("Sapo.").ListObjects("tab_1")  'adapter le nom de la feuille

J'ai aussi essayé avec le nom associé "Feuil14"

Très probablement que tab_1 n'est pas un tableau, mais une plage nommée de cellules !!!!
Dans ce cas :

Code VBA :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
Sub test()
Dim r As Range
Dim v As Variant
Dim p As Long
Dim d As Long
Dim i As Long
  Set r = Worksheets("Feuil1").Range("tab_1")  'adapter le nom de la feuille
  p = r.Row
  d = r.Rows.Count - p + 1
  For i = d To p Step -1
    v = r.Cells(i, 1).Value
    If IsError(v) Then
      If v = CVErr(XlCVError.xlErrNA) Or v = CVErr(XlCVError.xlErrRef) Then
        r.Rows(i).Delete
      End If
    Else
      If IsEmpty(v) Or v = 0 Then
        r.Rows(i).Delete
      End If
    End If
  Next i
End Sub

**shepard01** · 12/02/2020, 23h58

Envoyé par Patrice740

Très probablement que tab_1 n'est pas un tableau, mais une plage nommée de cellules !!!!

ok, une plage nommée de cellules.

J'ai testé ce dernier code en créant un tableau depuis un nouveau classeur et il fonctionne mais dès que je l'applique à mon classeur concernée j'ai la même erreur.

Sur cette feuille, il y a plusieurs tableaux j'applique la même méthode sur l'un d'entre eux (puis d'autres pour essayer) mais niet, ça ne marche pas.
Quelques choses fait qu'"il" ne reconnait pas ma plage nommée de cellules ou le nom de ma feuille.

**Patrice740** · 13/02/2020, 01h02

Re,

Je te conseille de créer des tableaux (et non pas des plages nommées) et d'utiliser le premier code, à mettre dans un module standard du classeur concerné.

**shepard01** · 13/02/2020, 10h29

Envoyé par Patrice740

Re, Je te conseille de créer des tableaux (et non pas des plages nommées) et d'utiliser le premier code, à mettre dans un module standard du classeur concerné.

Oui. C'est ce que j'ai fait et cela pour tout les tableaux de ma feuille. A l'avenir, j'utiliserai cette méthode pour tous.

Alors maintenant le problème est le suivant :
"Erreur d'exécution '1004': Nous ne pouvons effectuer cette action, car cela impliquerait le déplacement de cellules d'un tableau de votre feuille de calcul."

Code :

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

t.ListRows(i).Delete

Je crois comprendre que le code supprimerai bien la/les lignes vide/#N/A du tableau mais pas la ligne entière (hors tableau) ? ce qui entraînerai un décalage ?

Au niveau du contexte, j'ai 18 tableaux, contenants un nombres de colonnes différents presque à chaque fois (des vraies cette fois^^) et du texte entre chaque tableaux qui suivent le Tableau1. Donc une suppression de ligne ne doit pas interférer avec le reste, mais simplement "monter" l'ensemble lors de la suppression de la ligne complète.

L'objectif final étant d'appliquer ce code à chaque tableau.

**Patrice740** · 13/02/2020, 11h58

Re,

Effectivement ce code fonctionne si le tableaux sont tous dans des colonnes différentes
Si ils sont tous les uns en dessous des autres, il faut supprimer la ligne entière :

Code :

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

t.ListRows(i).Range.EntireRow.Delete

**shepard01** · 13/02/2020, 12h38

Envoyé par Patrice740

Re,

Effectivement ce code fonctionne si le tableaux sont tous dans des colonnes différentes
Si ils sont tous les uns en dessous des autres, il faut supprimer la ligne entière :

Code :

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

t.ListRows(i).Range.EntireRow.Delete

excellent.

Code :

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

  Set t = Worksheets("Feuil14.").ListObjects("Tableau_1")  'adapter le nom de la feuille

Pour appliquer le code du Tableau_1 à Tableau_10, je peux copier\coller le code 10fois (lourd en code) ou je peux mettre le nom des tableaux à la suite (j'en doute) ou que ce code soit une fonction que "j’appelle" à chaque tableau mais ça je ne saurai pas le faire.

**Patrice740** · 13/02/2020, 13h15

Envoyé par shepard01

Pour appliquer le code du Tableau_1 à Tableau_10, je peux copier\coller le code 10fois (lourd en code) ou je peux mettre le nom des tableaux à la suite (j'en doute) ou que ce code soit une fonction que "j’appelle" à chaque tableau mais ça je ne saurai pas le faire.

Une fonction est particulèrement conseillée lorsqu'elle est appelée depuis plusieurs procédures. Dans ton cas, il n'y aurait qu'un seul appel situé dans une boucle, ça clarifie le code mais c'est pas indispensable.

Pour balayer tous les tableaux il suffit de créer une boucle. Par exemple :

Code :

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

1
2
3
4
5
    Set WbO = Wb.Sheets(T_Cons_TBAC.Text)
    For Each t In WbO.lisobjects
        'Code de traitement du tableau
        '...
    Next t

Ou pour cibler certains tableaux :

Code :

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

1
2
3
4
5
6
7
    Set WbO = Wb.Sheets(T_Cons_TBAC.Text)
    For Each t In WbO.lisobjects
        If Left(t.Name, 8) = "Tableau_" Then
            'Code de traitement du tableau
            '...
        End If
    Next t

.

**shepard01** · 13/02/2020, 14h27

Envoyé par Patrice740

Pour balayer tous les tableaux il suffit de créer une boucle. Par exemple :

Code :

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

1
2
3
4
5
    Set WbO = Wb.Sheets(T_Cons_TBAC.Text)
    For Each t In WbO.lisobjects
        'Code de traitement du tableau
        '...
    Next t

Bien bien.. voici ma compilation :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
Private Sub Clear_Click()
Dim t As ListObject
Dim v As Variant
Dim i As Long
        Set Wbo = Wb.Sheets(T_Cons_TBAC.Text)
        For Each t In Wbo.lisobjects
        If Left(t.Name, 8) = "Tableau_" Then
            For i = t.ListColumns(1).DataBodyRange.Rows.Count To 1 Step -1
              v = t.ListColumns(1).DataBodyRange(i).Value
              If IsError(v) Then
                If v = CVErr(XlCVError.xlErrNA) Or v = CVErr(XlCVError.xlErrRef) Then
                  t.ListRows(i).Range.EntireRow.Delete
                End If
              Else
                If IsEmpty(v) Or v = 0 Then
                  t.ListRows(i).Range.EntireRow.Delete
                End If
              End If
            Next i
         End If
        Next t
End Sub

A l’exécution, le message suivant apparaît: " Erreur de compilation: Variable non définie" comment je dois déclarer la variable

Code :

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

T_Cons_TBAC

Et merci au faite

**Patrice740** · 13/02/2020, 15h34

Re,

Il manque la déclaration de WbO :

Code :

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

Dim WbO As Worksheet

Note qu'une « Sheet » n'est pas toujours une « Worksheet ».
Par exemple l'objet Sheet ne possède pas de propriété Range (il n'en hérite que si c'est une feuille de calcul).

Pour T_Cons_TBAC, il faut préciser le nom du formulaire à qui il appartient :

Code :

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

Set Wbo = Wb.Worksheets(UserForm1.T_Cons_TBAC.Text)

**shepard01** · 13/02/2020, 18h05

alors je dois aussi déclarer Wb :

Code :

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

Dim Wb As Variant

J'avoue je sais pas si c'est une variable.

et pour le nom du formulaire ? quel formulaire. J'ai fais un bouton sur la feuille concernée. Pas compris chef

**Patrice740** · 13/02/2020, 18h11

Envoyé par shepard01

alors je dois aussi déclarer Wb :

Code :

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

Dim Wb As Variant

J'avoue je sais pas si c'est une variable.
et pour le nom du formulaire ? quel formulaire. J'ai fais un bouton sur la feuille concernée. Pas compris chef

Pour le classeur :

Code :

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

Dim Wb as Workbook

Quel type de bouton sur la feuille : de formulaire ou ActiveX ?
Pourquoi lire le texte du bouton ? Il évolue ?

**shepard01** · 13/02/2020, 18h45

Envoyé par Patrice740

Quel type de bouton sur la feuille : de formulaire ou ActiveX ? Pourquoi lire le texte du bouton ? Il évolue ?

heu.. j'ai mis un bouton de commande ActiveX sur ma feuille. Quand je clique dessus, ça supprime les lignes vides|#N/A de mon tableau_1.
Du coup, lorsque je clique dessus il faudrait que ça fasse la boucle pour supprimer les lignes vides|#N/A des autres tableau_X à l'aide du code fourni précédemment.

En faite c'est une feuille que je rempli de données (varient d'une demande à l'autre d'où un certain nombre de ligne supplémentaire par anticipation). Parfois mes tableaux comporteront 3 ou 4 lignes parfois moins. Du coup, je cherche à supprimer toutes les lignes inutilisées d'un coup de poker si je puis dire.

**Patrice740** · 13/02/2020, 19h19

Re,

Ton code se trouve donc dans le module de feuille.

Inutile d'utiliser WbO (et wb), c'est plus simple :

Code VBA :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Option Explicit
Private Sub Clear_Click()
Dim t As ListObject
Dim v As Variant
Dim i As Long
        For Each t In Me.ListObjects
        If Left(t.Name, 8) = "Tableau_" Then
            For i = t.ListColumns(1).DataBodyRange.Rows.Count To 1 Step -1
              v = t.ListColumns(1).DataBodyRange(i).Value
              If IsError(v) Then
                If v = CVErr(XlCVError.xlErrNA) Or v = CVErr(XlCVError.xlErrRef) Then
                  t.ListRows(i).Range.EntireRow.Delete
                End If
              Else
                If IsEmpty(v) Or v = 0 Then
                  t.ListRows(i).Range.EntireRow.Delete
                End If
              End If
            Next i
         End If
        Next t
End Sub

**shepard01** · 13/02/2020, 23h00

Envoyé par Patrice740

Re,[/CODE]

Ah oui ! c'est parfait

. Très très utiles vos connaissances.

une tite dernière pour la route ?

Ça reprend le principe précédent. Je souhaiterai (je frotte la lampe magique Patrice) faire une boucle sur des cellules nommées commençant par Huile_X (exple : B68=Huile_1, B92=Huile_2 ect...) et si Huile_X est égal à zéro, alors supprimer la ligne de cette cellule et les 22 lignes en dessous de cette cellule.

En grand merci pour votre temps qui me permet d'en gagner encore plus.

Tester un cellule et action en fonction. [XL-2016]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager