J'ai une table comme ça

enter image description here

Et je veux filtrer en fonction de ce critère

  1. VALeur = entre 2000 et 3000
  2. Statut = 'FAUX'

Devenir comme ça, le filtre sur la colonne F & G

enter image description here

Et c'est mon code

Dim LastRow As Long
Dim i, Hide, popup  As Long
Dim message  As String
Dim LRow As Long
Dim sht As Worksheet
ActiveSheet.AutoFilterMode = False
    Columns.EntireColumn.Hidden = False
    Rows.EntireRow.Hidden = False
   Columns("O:R").EntireColumn.Delete
    Range("O:R").EntireColumn.Insert
    Range("F1").Value = "PO sTATUS"
    Range("G1").Value = "Value"

Set sht = ActiveSheet
LastRow = sht.Range("B" & Rows.Count).End(xlUp).Row



With sht.Range("F1:F" & LastRow)
    .Formula = "=B1"
   .Value = .Value
    .AutoFilter field:=1, Criteria1:="FALSE"
  End With

With sht.Range("G1:G" & LastRow)
    .Formula = "=D1"
   .Value = .Value
 .AutoFilter field:=1, Criteria1:=">=2000", Operator:=xlAnd, Criteria2:="<=3000"

   End With 

Et ce code n'affiche toujours que la ligne 1 sans valeurs entrez la description de l'image ici

0
kucluk 17 févr. 2020 à 10:21

1 réponse

Meilleure réponse

Essayez d'appliquer le filtre en une seule fois ?

Dim LastRow As Long
Dim i, Hide, popup  As Long
Dim message  As String
Dim LRow As Long
Dim sht As Worksheet

    ActiveSheet.AutoFilterMode = False
    Columns.EntireColumn.Hidden = False
    Rows.EntireRow.Hidden = False
    Columns("O:R").EntireColumn.Delete
    Range("O:R").EntireColumn.Insert
    Range("F1").Value = "PO sTATUS"
    Range("G1").Value = "Value"

Set sht = ActiveSheet
LastRow = sht.Range("B" & Rows.Count).End(xlUp).Row

With sht.Range("F1:F" & LastRow)
    .Formula = "=B1"
    .Value = .Value
End With

With sht.Range("G1:G" & LastRow)
    .Formula = "=D1"
    .Value = .Value
End With

With sht.Range("F1:G" & LastRow)
    .AutoFilter
    .AutoFilter field:=1, Criteria1:="FALSE"
    .AutoFilter field:=2, Criteria1:=">=2000", Operator:=xlAnd, Criteria2:="<=3000"
End With
1
Czeskleba 17 févr. 2020 à 13:19