Suppression des lignes contenant le message #ref!

Y compris Power BI, Power Query et toute autre question en lien avec Excel
S
SAM77170
Jeune membre
Jeune membre
Messages : 29
Inscrit le : 28 mars 2019
Version d'Excel : OFFICE 365

Message par SAM77170 » 11 avril 2019, 13:58

Bonjour à tous,

Je souhaiterais supprimer dans une feuille de calcul, à partir de la cellule "B3", toutes les lignes qui comporteraient le message d'erreur "#REF!", ce message n'étant référencé que dans la colonne"B".

En vous remerciant par avance pour vos propositions.

Bonne journée.
Avatar du membre
Florian53
Membre dévoué
Membre dévoué
Messages : 577
Appréciations reçues : 47
Inscrit le : 3 juin 2015
Version d'Excel : Office 365

Message par Florian53 » 11 avril 2019, 14:07

Bonjour,

:joindre:

Cordialement
Les grandes réussites sont le fruit de l'apprentissage durable. Apprenez à apprendre chaque jour.

:btres:
S
SAM77170
Jeune membre
Jeune membre
Messages : 29
Inscrit le : 28 mars 2019
Version d'Excel : OFFICE 365

Message par SAM77170 » 11 avril 2019, 15:28

J'ai mis une copie écran, le fichier est trop volumineux. Je souhaite la suppression de toutes les lignes comportant le message d'erreur #REF!. Ce dernier est généré par la fonction =RECHERCHEV(A2;'J-1 base travail referentiel'!A:B;2;0) se trouvant dans la cellule B2.

Ce message d'erreur est dupliqué dans la colonne B jusqu'à la fin de la feuille de calcul, soit jusqu'à la ligne 1048576, ce qui entraîne une taille excessive pour ce fichier de 28 000 Ko...

Bonne réception.
Doc2.docx
(104.52 Kio) Téléchargé 6 fois
Avatar du membre
Florian53
Membre dévoué
Membre dévoué
Messages : 577
Appréciations reçues : 47
Inscrit le : 3 juin 2015
Version d'Excel : Office 365

Message par Florian53 » 11 avril 2019, 15:53

Tu aurais pu envoyer un fichier excel simplifié juste avec une dizaine de ligne pour l'exemple plutôt que d'envoyer une copie d'écran.

Pour répondre à ta question j'essaierai de mettre en place un code comme ci dessous pour traiter les lignes en erreur :
Private Sub test()
    Dim sh As Worksheet
    Dim Derlgn As Long
    Dim i As Long
    
        Set sh = Sheets("Feuil1")
        Derlgn = Range("A" & Rows.Count).End(xlUp).Row
        
        For i = 1 To Derlgn
            If IsError(Cells(i, 2)) Then Rows(i).Delete
        Next i
End Sub
Si tu comme tu dispose d’énormément de lignes le code peut être très long à s’exécuter et là il faudra passer par un filtre pour un temps d’exécution immédiat.
Les grandes réussites sont le fruit de l'apprentissage durable. Apprenez à apprendre chaque jour.

:btres:
S
SAM77170
Jeune membre
Jeune membre
Messages : 29
Inscrit le : 28 mars 2019
Version d'Excel : OFFICE 365

Message par SAM77170 » 11 avril 2019, 15:54

Je te remercie pour ta réponse. Je vais l'essayer et je te tiens au courant. Merci.
S
SAM77170
Jeune membre
Jeune membre
Messages : 29
Inscrit le : 28 mars 2019
Version d'Excel : OFFICE 365

Message par SAM77170 » 11 avril 2019, 16:09

ça n'a pas fonctionné. j'ai défini pour le RANGE, à partir de la cellule A3.

Cela bloque au niveau de Derlgn...

Comment puis-je t'envoyer une fiche EXCEL simplifiée ?.

Dim sh As Worksheet
Dim Derlgn As Long
Dim w As Long

Set sh = Sheets("J base travail referentiel")
Derlgn = Range("A3" & Rows.Count).End(xlUp).Row

For w = "A3" To Derlgn
If IsError(Cells(w, 2)) Then Rows(w).Delete
Next w
Avatar du membre
Florian53
Membre dévoué
Membre dévoué
Messages : 577
Appréciations reçues : 47
Inscrit le : 3 juin 2015
Version d'Excel : Office 365

Message par Florian53 » 11 avril 2019, 16:13

Fourni seulement un exemple avec une dizaine de ligne seulement, sinon essaye comme ceci:
Dim sh As Worksheet
Dim Derlgn As Long
Dim w As Long

Set sh = Sheets("J base travail referentiel")
Derlgn = sh.Range("A" & Rows.Count).End(xlUp).Row

For w = 3 To Derlgn
If IsError(sh.Cells(w, 2)) Then Rows(w).Delete
Next w
Sinon avec la méthode du filtre ( a adapter ) :
Private Sub efface()
Sheets("Feuil1").Range("A1").Select
Selection.AutoFilter Field:=2, Criteria1:="#REF!", Operator:=xlAnd
Sheets("Feuil1").Range("a2:a" & Range("a65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub
Les grandes réussites sont le fruit de l'apprentissage durable. Apprenez à apprendre chaque jour.

:btres:
S
SAM77170
Jeune membre
Jeune membre
Messages : 29
Inscrit le : 28 mars 2019
Version d'Excel : OFFICE 365

Message par SAM77170 » 11 avril 2019, 16:38

OK merci pour toutes ces propositions. Je vais les essayer.
Avatar du membre
78chris
Passionné d'Excel
Passionné d'Excel
Messages : 4'526
Appréciations reçues : 339
Inscrit le : 9 juillet 2017
Version d'Excel : 2010 à 2019 + 365

Message par 78chris » 11 avril 2019, 16:58

Bonjour à tous

Le problème initial c'est de mettre une formule sur 1 million de lignes !

C'est un non sens, surtout avec Office 365

Il faut revoir le problème en amont pas corriger l'erreur en aval...
Chris
Quand un homme a faim, mieux vaut lui apprendre à pêcher que de lui donner un poisson.
Confucius
G
GNIN
Membre habitué
Membre habitué
Messages : 73
Appréciations reçues : 6
Inscrit le : 6 février 2019
Version d'Excel : 2003
Version de Calc : 4.3

Message par GNIN » 11 avril 2019, 17:29

Bonjour,

Exact, je suis d'accord avec 78Chris, le problème est à revoir en amont.
Toutefois si tu persistes dans cette voie, je te propose la solution suivante qui utilise une fonction ErreurRef perso.
(Nom de l'onglet à modifier, "Feuil1" dans l'exemple)
Sub test()
    Dim C As Range
    Dim i As Long
    'Dernière ligne utilisée de la colonne B de l'onglet "Feuil1"
    'stockée dans la variable i
    Set C = Sheets("Feuil1").Cells(65535, 2).End(xlUp)
    i = C.Row
    
    'Boucle qui démarre du bas
    'c'est le mieux en raison des suppressions de lignes
    Do
        'Si #REF on supprime la ligne
        If ErreurREF(Sheets("Feuil1").Cells(i, 2)) Then
            Sheets("Feuil1").Cells(i, 2).EntireRow.Delete
        End If
        i = i - 1
    Loop Until i = 0 'Arrêt après traitement de la ligne 1
End Sub

Function ErreurREF(C As Range) As Boolean
    'Constantes Excel des erreurs
    'xlErrDiv, xlErrNa, xlErrName, xlErrNum, xlErrNull, xlErrRef, xlErrValue
    ErreurREF = False
    If IsError(C.Value) Then
        If C.Value = CVErr(xlErrRef) Then
            ErreurREF = True
        Else
            ErreurREF = False
        End If
    End If 
End Function


Bon courage pour la suite
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message