XL 2013 Filtrer les #REF!

michir

XLDnaute Occasionnel
Bonsoir
existe t il une technique permettant de filtrer les valeurs d'erreur #REF! dans un fichier important
Merci
 

Dranreb

XLDnaute Barbatruc
Menu Accueil, groupe Édition, commande Rchercher et sélectionner, sous-commande Atteindre…,
bouton Cellules…, Formules et ne laisser coché que Erreurs
 

michir

XLDnaute Occasionnel
Oui merci Dranreb, mais cela fonctionne que sur dse cellules avec formules , mais j'ai des #REF! dans des cellules qui sont dans le dur (sans formules)
il faut dire que ces cellules ont été transformées en valeurs par copier coller justement pour écraser les formules après qu'elles aient rempli leur office
 

DoubleZero

XLDnaute Barbatruc
Bonjour, michir, Dranreb, le Forum,

Peut-être ainsi :
VB:
Option Explicit
Sub Erreurs_REF_filtrer()
' colonnes adapter
    Range("a1:b65000").AutoFilter Field:=2, Criteria1:="#REF!"
End Sub
A bientôt :)
 

job75

XLDnaute Barbatruc
Bonsoir michir, Bernard, chère ânesse,
Oui merci Dranreb, mais cela fonctionne que sur dse cellules avec formules , mais j'ai des #REF! dans des cellules qui sont dans le dur (sans formules)
Allons donc vous n'avez pas vu que cette commande recherche soit des formules soit des constantes ?

A+
 

job75

XLDnaute Barbatruc
Re,

Pour mettre en évidence les valeurs d'erreur il suffit de masquer les lignes qui n'en contiennent pas :
Code:
Sub AfficherErreurs()
On Error Resume Next 'si pas de SpecialCells
With ActiveSheet.UsedRange
  With .Columns(.Columns.Count + 1)
    .FormulaR1C1 = "=1/SUMPRODUCT(-ISERROR(RC1:RC[-1]))"
    .SpecialCells(xlCellTypeFormulas, 16).EntireRow.Hidden = True
    .ClearContents 'neutraliser si l'on veut voir la formule
  End With
End With
End Sub
A+
 

job75

XLDnaute Barbatruc
Bonjour le fil, le forum,

J'ai testé la macro précédente avec des tableaux contenant des #REF! répartis aléatoirement :

- tableau1 : 20 x 10 000 => 200 000 cellules => 200 #REF! => 1,3 seconde

- tableau2 : 20 x 100 000 => 2 000 000 cellules => 2 000 #REF! => 126 secondes !!!

Donc cette macro ne convient pas pour traiter de très grands tableaux.

La bonne solution consiste à utiliser le filtre avancé :
Code:
Sub AfficherErreurs()
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
  .Rows(1).EntireRow.Insert
  .Rows(0) = "=A&COLUMN()" 'ligne de titres
  .Cells(1, .Columns.Count + 1) = "=SUMPRODUCT(-ISERROR(RC1:RC[-1]))" 'critère
  .Rows(0).Resize(.Rows.Count + 1).AdvancedFilter xlFilterInPlace, .Cells(0, .Columns.Count + 1).Resize(2)
  .Cells(1, .Columns.Count + 1) = "" 'RAZ
  .Rows(0).EntireRow.Delete
End With
End Sub

Sub AfficherTout()
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
End Sub
Sur le tableau2 la macro s'exécute en 0,6 seconde.

Bonne journée.
 
Dernière édition:

michir

XLDnaute Occasionnel
merci à vous trois
Job75, comment tu mesures le temps d'exécution de la macro ?
Tu as peut être oublié de joindre le tableau dont tu parles ?
merci encore
 

job75

XLDnaute Barbatruc
Re,

Je n'avais pas joint le fichier parce que je pensais que vous étiez un grand garçon...
Code:
Sub AfficherErreurs()
'Ctrl+E pour lancer la macro
Dim t#
t = Timer
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
  .Rows(1).EntireRow.Insert
  .Rows(0) = "=A&COLUMN()" 'ligne de titres
  .Cells(1, .Columns.Count + 1) = "=SUMPRODUCT(-ISERROR(RC1:RC[-1]))" 'critère
  .Rows(0).Resize(.Rows.Count + 1).AdvancedFilter xlFilterInPlace, .Cells(0, .Columns.Count + 1).Resize(2)
  .Cells(1, .Columns.Count + 1) = "" 'RAZ
  .Rows(0).EntireRow.Delete
End With
MsgBox "Durée " & Format(Timer - t, "0.00 \s")
End Sub
A+
 

Fichiers joints

Haut Bas