Suppression des lignes contenant le message #ref

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.

Bonjour,

Cordialement

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.

18doc2.docx (104.52 Ko)

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.

Je te remercie pour ta réponse. Je vais l'essayer et je te tiens au courant. Merci.

ç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

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

OK merci pour toutes ces propositions. Je vais les essayer.

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...

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

Rechercher des sujets similaires à "suppression lignes contenant message ref"