1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| Sub MoveByAdvancedFilter()
' http://philippe.tulliez.be
Application.ScreenUpdating = False
' Déclaration des variables et constantes
Const myFormula = "=OR(J2=""VW"",J2=""Toyota"")"
Const EvalFormula = "=DCOUNTA([Data],A1,[Criteria])"
Dim areaSource As Range, areaCriteria As Range, areaTarget As Range
' Zone Data (Source) & Export (Cible)
With ThisWorkbook
Set areaSource = .Worksheets("db").Range("A1").CurrentRegion
Set areaTarget = .Worksheets("Export").Range("A1")
End With
' Zone des critères 2 colonnes à droite de la zone Data
With areaSource
Set areaCriteria = .Resize(2, 1).Offset(columnoffset:=.Columns.Count + 1)
End With
areaCriteria(1) = "_fn_"
areaCriteria(2) = myFormula
If Evaluate(Replace(Replace(EvalFormula, "[Data]", areaSource.Address), "[Criteria]", areaCriteria.Address)) Then
With areaSource
.AdvancedFilter xlFilterCopy, areaCriteria, areaTarget ' Exportation des données
.AdvancedFilter xlFilterInPlace, areaCriteria ' Filtre sur place
areaCriteria.Clear ' Suppression des critères
' Suppression des lignes filtrées
.Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp
With .Worksheet: .Activate: .ShowAllData: End With ' Supprime le filtre
End With
Else
MsgBox "Pas d'éléments correspondant aux critères"
End If
Application.ScreenUpdating = True
Set areaSource = Nothing: Set areaTarget = Nothing: Set areaCriteria = Nothing
End Sub |
Partager