Sub Filtrer_les_1()
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Range("Q6", Range("Q" & Rows.Count).End(xlUp))
.Replace 1, "#N/A", xlWhole
.EntireRow.Hidden = True 'masque tout
.SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = False 'affiche
.Replace "#N/A", 1
End With
End Sub
Sub RAZ()
Rows.Hidden = False 'affiche tout
End Sub
Bonjour JobBonsoir chaelie2015, Phil69970, mapomme,
Bonne nuit.VB:Sub Filtrer_les_1() Application.ScreenUpdating = False On Error Resume Next 'si aucune SpecialCell If FilterMode Then ShowAllData 'si la feuille est filtrée With Range("Q6", Range("Q" & Rows.Count).End(xlUp)) .Replace 1, "#N/A", xlWhole .EntireRow.Hidden = True 'masque tout .SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = False 'affiche .Replace "#N/A", 1 End With End Sub Sub RAZ() Rows.Hidden = False 'affiche tout End Sub
@chaelie2015
Dans le cas ou j'ai des cellules fusionnées
Sub Filtrer_les_1()
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Range("Q6:Q" & Cells.SpecialCells(xlCellTypeLastCell).Row)
.Replace 1, "#N/A", xlWhole
.EntireRow.Hidden = True 'masque tout
.SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = False 'affiche
.Replace "#N/A", 1
End With
End Sub
Sub RAZ()
Rows.Hidden = False 'affiche tout
End Sub
Sub FiltrerB_les_1()
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Range("T6:T" & Cells.SpecialCells(xlCellTypeLastCell).Row)
.Replace 1, "#N/A", xlWhole
.EntireRow.Hidden = True 'masque tout
.SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = False 'affiche
.Replace "#N/A", 1
End With
End Sub
je souhaite encore conditionner le code par le filtre de l'Excel selon le filtre de la colonne K.
Sub Filtrer_les_1()
Dim P As Range
On Error Resume Next 'si aucune SpecialCell
RAZ
With Range("Q6:Q" & Cells.SpecialCells(xlCellTypeLastCell).Row)
Set P = .SpecialCells(xlCellTypeVisible)
.Replace 1, "#N/A", xlWhole
.EntireRow.Hidden = True 'masque tout
Intersect(P, .SpecialCells(xlCellTypeConstants, 16)).EntireRow.Hidden = False 'affiche
.Replace "#N/A", 1
End With
End Sub
Sub RAZ()
Application.ScreenUpdating = False
If FilterMode Then ShowAllData 'si la feuille est filtrée
With Range("K6:K" & Cells.SpecialCells(xlCellTypeLastCell).Row)
.Rows.Hidden = False 'affiche tout
.AutoFilter 1, "BEL" 'critère à adapter
End With
End Sub
Sub FiltrerB_les_1()
Dim P As Range
On Error Resume Next 'si aucune SpecialCell
RAZ
With Range("T6:T" & Cells.SpecialCells(xlCellTypeLastCell).Row).SpecialCells(xlCellTypeVisible)
Set P = .SpecialCells(xlCellTypeVisible)
.Replace 1, "#N/A", xlWhole
.EntireRow.Hidden = True 'masque tout
Intersect(P, .SpecialCells(xlCellTypeConstants, 16)).EntireRow.Hidden = False 'affiche
.Replace "#N/A", 1
End With
End Sub
ReSalut Eric C.VB:Sub Filtrer_les_1() Dim P As Range Application.ScreenUpdating = False On Error Resume Next 'si aucune SpecialCell RAZ With Range("Q6:Q" & Cells.SpecialCells(xlCellTypeLastCell).Row) Set P = .SpecialCells(xlCellTypeVisible) .Replace 1, "#N/A", xlWhole .EntireRow.Hidden = True 'masque tout Intersect(P, .SpecialCells(xlCellTypeConstants, 16)).EntireRow.Hidden = False 'affiche .Replace "#N/A", 1 End With End Sub Sub RAZ() Application.ScreenUpdating = False If FilterMode Then ShowAllData 'si la feuille est filtrée With Range("K6:K" & Cells.SpecialCells(xlCellTypeLastCell).Row) .Rows.Hidden = False 'affiche tout .AutoFilter 1, "BEL" 'critère à adapter End With End Sub Sub FiltrerB_les_1() Dim P As Range Application.ScreenUpdating = False On Error Resume Next 'si aucune SpecialCell RAZ With Range("T6:T" & Cells.SpecialCells(xlCellTypeLastCell).Row).SpecialCells(xlCellTypeVisible) Set P = .SpecialCells(xlCellTypeVisible) .Replace 1, "#N/A", xlWhole .EntireRow.Hidden = True 'masque tout Intersect(P, .SpecialCells(xlCellTypeConstants, 16)).EntireRow.Hidden = False 'affiche .Replace "#N/A", 1 End With End Sub
Bjr JOB ,forumIl faut se donner a priori le critère de filtrage en colonne K.
Autrement ce serait une usine à gaz.