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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
| Public filterArray()
Sub Main()
Dim I As Integer
Dim arrSh As Variant
Dim Sh As Worksheet
'Les feuilles que je veux traiter
arrSh = Array("Feuil1", "Feuil2", "Feuil3")
For I = 0 To UBound(arrSh)
Set Sh = Worksheets(arrSh(I))
StoreFilters Sh
'Traitement
'Ici j'enlève les filtres pour fins de test
Sh.AutoFilterMode = False
Sh.Rows(1).AutoFilter
Stop 'on peut vérifier l'état des filtres
RestoreFilters Sh
Next
Set Sh = Nothing
End Sub
Sub StoreFilters(Sh As Worksheet)
Dim I As Long, J As Long
Dim currentFiltRange As String
On Error GoTo Erreur
With Sh.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(1 To .Count, 1 To 3)
For I = 1 To .Count
With .Item(I)
If .On Then
If IsArray(.Criteria1) Then
For J = 1 To UBound(.Criteria1)
filterArray(I, 1) = filterArray(I, 1) & .Criteria1(J) & "|"
Next
Else
filterArray(I, 1) = .Criteria1
If .Operator Then
filterArray(I, 2) = .Operator
filterArray(I, 3) = .Criteria2
End If
End If
End If
End With
Next
End With
End With
Exit Sub
Erreur:
MsgBox Err.Description
End Sub
Sub RestoreFilters(Sh As Worksheet)
Dim I As Long, J As Long, Idx As Long
Dim Tablo
Dim arrTemp() As String
On Error GoTo Erreur
ReDim arrTemp(0)
For I = 1 To UBound(filterArray)
If InStr(1, filterArray(I, 1), "|") > 0 Then
Tablo = Split(filterArray(I, 1), "|")
For J = 0 To UBound(Tablo)
If Tablo(J) <> "" Then
ReDim Preserve arrTemp(Idx)
arrTemp(Idx) = Replace(Tablo(J), "=", "")
Idx = Idx + 1
End If
Next
Sh.Rows(1).AutoFilter Field:=I, Criteria1:=arrTemp, Operator:=xlFilterValues
Else
If Not IsEmpty(filterArray(I, 1)) And Not IsEmpty(filterArray(I, 2)) Then
Sh.Rows(1).AutoFilter Field:=I, Criteria1:=filterArray(I, 1), Operator:=filterArray(I, 2), Criteria2:=filterArray(I, 3)
ElseIf Not IsEmpty(filterArray(I, 1)) Then
Sh.Rows(1).AutoFilter Field:=I, Criteria1:=filterArray(I, 1)
End If
End If
Next
Exit Sub
Erreur:
MsgBox Err.Description
End Sub |
Partager