Macro qui filtre une colonne de formules

Le 09/03/2018 à 14:33

Bonjour à tous.

Ma recherche sur ce forum (et ailleurs) n'ayant rien donné, je me permets de vous soumettre mon problème.

Étant nul en VBA, j'ai enregistré une macro qui, entre autre, filtre une colonne.

Cette colonne contient dans ses cellules une formule dont le résultat est VRAI ou FAUX.

La macro doit filtrer cette colonne pour n'afficher que les VRAI.

Et le résultat de la macro est qu'elle n'affiche rien du tout, seulement la 1ère ligne. Ni les VRAI, ni les FAUX.

Pourtant cette fonction VBA fonctionne pour d'autre colonnes dont les cellules ne contiennent que du texte, pas de formule ...

Voici la formule affichant les VRAI ou FAUX dans ma colonne AE :

=SI(ET(C2>=(SERIE.JOUR.OUVRE(AUJOURDHUI();-1));C2<=(SERIE.JOUR.OUVRE(AUJOURDHUI();2)));VRAI;FAUX)

Et voici le bout de code VBA qui ne semble pas fonctionner correctement :

ActiveSheet.Range("AE1").AutoFilter Field:=31, Criteria1:="VRAI"

Si quelqu'un peut trouver pourquoi le filtre ne fonctionne pas ...

Merci d'avance !

Le 09/03/2018 à 17:27

Bonjour Derje,

Je te propose :

    ActiveSheet.Range("AE1").AutoFilter
    ActiveSheet.Range("AE:AE").AutoFilter Field:=1, Criteria1:="VRAI"

Le 12/03/2018 à 09:38

Merci GVIALLES mais ça ne fonctionne pas : ta solution filtre la 1ère colonne (A:A) sur VRAI.

Et c'est la colonne AE:AE qui doit être filtrée.

Le 12/03/2018 à 11:14

Bonjour Derje,

Le code proposé fonctionne correctement sur la colonne "AE" chez moi.

Peux-tu fournir un exemplaire anonymisé de ton classeur?

Le 13/03/2018 à 10:18

16test.zip (89.89 Ko)

avec la macro VBA complète (mais c'est la fin qui m'intéresse) :

Sub test()
' test
    Cells.Select
    ActiveWindow.ScrollRow = 1
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
       .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 16.71
    Selection.RowHeight = 24.75
    Rows("1:1").Select
    Selection.RowHeight = 37.5
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    ActiveWindow.SmallScroll ToRight:=3
    Columns("K:U").Select
    Range("U1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("O:O").Select
    Columns("P:T").Select
    Selection.Delete Shift:=xlToLeft
    Columns("R:S").Select
    Selection.Delete Shift:=xlToLeft
    Columns("S:T").Select
    Range("T1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("T:T").Select
    Columns("T:X").Select
    Range("X1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("Z:AT").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AC:BI").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AD:AD").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AE:AP").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Rows("1:1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A1").Select
    Columns("A:A").ColumnWidth = 9.43
    Columns("B:B").ColumnWidth = 13.43
    Columns("C:C").ColumnWidth = 13.57
    Columns("D:D").ColumnWidth = 14.29
    Columns("D:D").ColumnWidth = 13
    Columns("E:E").ColumnWidth = 14.71
    Columns("F:F").ColumnWidth = 13.57
    Columns("G:G").ColumnWidth = 13.71
    Columns("G:G").ColumnWidth = 14.86
    Columns("H:H").ColumnWidth = 12.57
   Columns("H:H").ColumnWidth = 12.29
    Columns("I:I").ColumnWidth = 13.14
    Columns("I:I").ColumnWidth = 14
    Columns("J:J").ColumnWidth = 13.57
    Columns("K:K").ColumnWidth = 8.57
    Columns("K:K").ColumnWidth = 9.43
    Columns("L:L").ColumnWidth = 51.71
    Columns("M:M").ColumnWidth = 9.71
    Columns("M:M").ColumnWidth = 7.86
    Columns("N:N").ColumnWidth = 23.43
    Columns("O:O").ColumnWidth = 6.43
    Columns("O:O").ColumnWidth = 5.71
    Columns("P:P").ColumnWidth = 6.71
    Columns("P:P").ColumnWidth = 5.71
    Columns("Q:Q").ColumnWidth = 4.86
    Columns("Q:Q").ColumnWidth = 4.43
    Columns("R:R").ColumnWidth = 4.86
    Columns("R:R").ColumnWidth = 6.29
    Columns("R:R").ColumnWidth = 7.29
    Columns("S:S").ColumnWidth = 45.71
    Columns("S:S").ColumnWidth = 51
    Columns("T:T").ColumnWidth = 10.71
    Columns("U:U").ColumnWidth = 8.71
    Columns("V:V").ColumnWidth = 8
    Columns("W:W").ColumnWidth = 7.43
    Columns("W:W").ColumnWidth = 9
    Columns("X:X").ColumnWidth = 7.86
    Columns("Y:Y").ColumnWidth = 7.43
    Columns("Z:Z").ColumnWidth = 7.29
    Columns("Z:Z").ColumnWidth = 5.43
    Columns("AA:AA").ColumnWidth = 5
    Columns("AB:AB").ColumnWidth = 5
    Columns("AC:AC").ColumnWidth = 13.71
    Columns("S:S").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("N:N").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 7
    Columns("L:L").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Columns("R:R").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("Q:Q").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("K:K").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 13.29
    Columns("AC:AC").ColumnWidth = 10
    Columns("AD:AD").ColumnWidth = 9
'
Columns("AC:AC").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'
    Range("X:X,AD:AD").Select
    Range("AD1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
       .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("T:AB").Select
    Selection.EntireColumn.Hidden = True
        Range("A1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
' pour filtrer sur les activités d'hier à après-demain
    Range("AE1").Select
    ActiveCell.Value = "Tâche réalisation :" & Chr(10) & "J-1 à J+2"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-28]>(WORKDAY(TODAY(),-2)),RC[-28]<(WORKDAY(TODAY(),3))),TRUE,FALSE)"
    Range("AE2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AE5000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    ' pour mettre les filtres
    Rows("1:1").Select
    Selection.AutoFilter
    ' pour filtrer sur GCV ou IPE
    ActiveSheet.Range("$A$1:$EA$5000").AutoFilter Field:=17, Criteria1:= _
        "=*GCV*", Operator:=xlOr, Criteria2:="=*IPE*"
    ' pour filtrer sur "J-1 à J+2 : VRAI" mais fonctionne mal
    ActiveSheet.Range("AE1").AutoFilter Field:=31, Criteria1:="VRAI"

End Sub

Le 13/03/2018 à 14:21

Je te propose de remplacer le code fourni par :

Sub test()
' test
    Cells.Select
    ActiveWindow.ScrollRow = 1
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
       .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 16.71
    Selection.RowHeight = 24.75
    Rows("1:1").Select
    Selection.RowHeight = 37.5
    Cells.Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:I").Select
    Selection.Delete Shift:=xlToLeft
    Columns("K:K").Select
    ActiveWindow.SmallScroll ToRight:=3
    Columns("K:U").Select
    Range("U1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("O:O").Select
    Columns("P:T").Select
    Selection.Delete Shift:=xlToLeft
    Columns("R:S").Select
    Selection.Delete Shift:=xlToLeft
    Columns("S:T").Select
    Range("T1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("T:T").Select
    Columns("T:X").Select
    Range("X1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("Z:AT").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AC:BI").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AD:AD").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AE:AP").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Rows("1:1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A1").Select
    Columns("A:A").ColumnWidth = 9.43
    Columns("B:B").ColumnWidth = 13.43
    Columns("C:C").ColumnWidth = 13.57
    Columns("D:D").ColumnWidth = 14.29
    Columns("D:D").ColumnWidth = 13
    Columns("E:E").ColumnWidth = 14.71
    Columns("F:F").ColumnWidth = 13.57
    Columns("G:G").ColumnWidth = 13.71
    Columns("G:G").ColumnWidth = 14.86
    Columns("H:H").ColumnWidth = 12.57
   Columns("H:H").ColumnWidth = 12.29
    Columns("I:I").ColumnWidth = 13.14
    Columns("I:I").ColumnWidth = 14
    Columns("J:J").ColumnWidth = 13.57
    Columns("K:K").ColumnWidth = 8.57
    Columns("K:K").ColumnWidth = 9.43
    Columns("L:L").ColumnWidth = 51.71
    Columns("M:M").ColumnWidth = 9.71
    Columns("M:M").ColumnWidth = 7.86
    Columns("N:N").ColumnWidth = 23.43
    Columns("O:O").ColumnWidth = 6.43
    Columns("O:O").ColumnWidth = 5.71
    Columns("P:P").ColumnWidth = 6.71
    Columns("P:P").ColumnWidth = 5.71
    Columns("Q:Q").ColumnWidth = 4.86
    Columns("Q:Q").ColumnWidth = 4.43
    Columns("R:R").ColumnWidth = 4.86
    Columns("R:R").ColumnWidth = 6.29
    Columns("R:R").ColumnWidth = 7.29
    Columns("S:S").ColumnWidth = 45.71
    Columns("S:S").ColumnWidth = 51
    Columns("T:T").ColumnWidth = 10.71
    Columns("U:U").ColumnWidth = 8.71
    Columns("V:V").ColumnWidth = 8
    Columns("W:W").ColumnWidth = 7.43
    Columns("W:W").ColumnWidth = 9
    Columns("X:X").ColumnWidth = 7.86
    Columns("Y:Y").ColumnWidth = 7.43
    Columns("Z:Z").ColumnWidth = 7.29
    Columns("Z:Z").ColumnWidth = 5.43
    Columns("AA:AA").ColumnWidth = 5
    Columns("AB:AB").ColumnWidth = 5
    Columns("AC:AC").ColumnWidth = 13.71
    Columns("S:S").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("N:N").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveWindow.ScrollColumn = 7
    Columns("L:L").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Columns("R:R").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("Q:Q").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
   With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("K:K").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 13.29
    Columns("AC:AC").ColumnWidth = 10
    Columns("AD:AD").ColumnWidth = 9
'
Columns("AC:AC").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'
    Range("X:X,AD:AD").Select
    Range("AD1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
       .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("T:AB").Select
    Selection.EntireColumn.Hidden = True
        Range("A1").Select
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
' pour filtrer sur les activités d'hier à après-demain
    Range("AE1").Select
    ActiveCell.Value = "Tâche réalisation :" & Chr(10) & "J-1 à J+2"
    Range("AE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-28]>(WORKDAY(TODAY(),-2)),RC[-28]<(WORKDAY(TODAY(),3))),TRUE,FALSE)"
    Range("AE2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AE5000").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    ' pour mettre les filtres
    Dim oSheet As Excel.Worksheet
    Set oSheet = ThisWorkbook.Worksheets(1)
    'Vérifier si les filtres sont actifs sur la feuille
    If oSheet.AutoFilterMode Then
        oSheet.AutoFilterMode = False
    End If

    oSheet.Rows(1).AutoFilter
    ' pour filtrer sur GCV ou IPE
    oSheet.UsedRange.AutoFilter Field:=17, Criteria1:="=*GCV*", Operator:=xlOr, Criteria2:="=*IPE*"
    ' pour filtrer sur les activités d'hier à après-demain
    oSheet.UsedRange.AutoFilter Field:=31, Criteria1:="VRAI"

NB : Seule la partie finale du code traitant le filtrage a été modifiée

Le 13/03/2018 à 15:14

Merci GVIALLES mais ça ne fonctionne pas : j'ai droit au message d'erreur

Erreur d'exécution '1004': la commande n'a pas pu être exécutée avec la plage spécifiée. Sélectionnez une seule cellule dans la plage et réessayez.

Le 13/03/2018 à 15:38

Y aurait-il une protection sur la feuille et des cellules vérouillées?

Le 13/03/2018 à 15:41

10testfiltres.xlsm (70.64 Ko)

Le 13/03/2018 à 15:48

GVIALLES a écrit :
Y aurait-il une protection sur la feuille et des cellules vérouillées?

Non !

Rechercher des sujets similaires à "macro qui filtre colonne formules"