Sub filtreannée()
With ActiveSheet.Range("$B$3", Cells(Rows.Count, "B").End(xlUp))
.AutoFilter Field:=1, Criteria2:=Array(0, "12/1/" & [d1].Value), Operator:=xlFilterValues
End With
End Sub
Sub Filtre_ANNEE()
D_AN = CLng(CDate("1/1/" & [D1]))
F_AN = CLng(CDate("31/12/" & [D1]))
ActiveSheet.Range("$B$1:$B$678").AutoFilter Field:=1, Criteria1:=">=" & D_AN, Operator:=xlAnd, Criteria2:="<=" & F_AN
End Sub
C'est juste.Bonjour
Affecte la macro "filtreannée" a ta ton boutonVB:Sub filtreannée() With ActiveSheet.Range("$B$3", Cells(Rows.Count, "B").End(xlUp)) .AutoFilter Field:=1, Criteria2:=Array(0, "12/1/" & [d1].Value), Operator:=xlFilterValues End With End Sub
et met ta D1 en format nombre
Merci beaucoupBonjour aussi à toi patricktoulon...
J'avais fait dans le basique
(comme c'est fait je poste)
NB: Comme précédemment indiqué, code adapté du code fourni par l'enregistreur de macros.
PS: Adapter la plage des cellules selon le classeur avant de tester.VB:Sub Filtre_ANNEE() D_AN = CLng(CDate("1/1/" & [D1])) F_AN = CLng(CDate("31/12/" & [D1])) ActiveSheet.Range("$B$1:$B$678").AutoFilter Field:=1, Criteria1:=">=" & D_AN, Operator:=xlAnd, Criteria2:="<=" & F_AN End Sub
Sub Macro1()
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("$B$3:$B$680").AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/31/2018")
End Sub
Private Sub Worksheet_Change(ByVal T As Range)
If T.Address = "$D$1" Then
Intersect(UsedRange.EntireRow, [B:B]).Offset(2).AutoFilter Field:=1, Operator:=7, Criteria2:=Array(0, "12/31/" & T)
Else
AutoFilterMode = False
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [D1]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
With [A2].CurrentRegion.Columns(2)
.Rows.Hidden = False 'affiche tout
If [D1] = "" Then Exit Sub
.Replace [D1], "µ", xlPart
.Rows.Hidden = True 'masque tout
.SpecialCells(xlCellTypeConstants, 2).Rows.Hidden = False 'affiche les textes
.Replace "µ", [D1]
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [D1]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
With [A2].CurrentRegion.Columns(4)
.Rows.Hidden = False 'affiche tout
If [D1] = "" Then Exit Sub
.Formula = "=IF(ISNUMBER(B2),IF(YEAR(B2)<>D$1,1))"
.SpecialCells(xlCellTypeFormulas, 1).Rows.Hidden = True 'masque les nombres
.Value = ""
End With
End Sub