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
 

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
 

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:

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+
 

Pièces jointes

  • AfficherErreurs(1).xlsm
    5.3 MB · Affichages: 35

Discussions similaires

Statistiques des forums

Discussions
311 733
Messages
2 082 015
Membres
101 868
dernier inscrit
pierreselo33