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
|
Option Explicit
Sub TestFilt()
Filt Feuil1
End Sub
Sub Filt(ByVal Sh As Worksheet)
'
' Filt Macro
'
' Touche de raccourci du clavier: Ctrl+Shift+K
Dim DerniereLigne As Long, DerniereColonne As Long, ColonneValeurs As Long
Dim AireValeurs As Range
With Sh
If .FilterMode = True Then
.ShowAllData
End If
DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
DerniereColonne = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set AireValeurs = .Range(.Cells(2, "T"), .Cells(DerniereLigne, "T"))
With AireValeurs
ColonneValeurs = .Column
.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.HorizontalAlignment = xlRight
With .Offset(0, DerniereColonne + 1 - ColonneValeurs)
.Formula = "=T2*1"
.NumberFormat = "0.00"
End With
End With
.Cells(1, DerniereColonne + 1) = "Valeurs"
TrierFiltrerUnTableau Sh, 1, DerniereColonne + 1, 5#
End With
Set AireValeurs = Nothing
End Sub
Sub TrierFiltrerUnTableau(ByVal FeuilleATrier As Worksheet, ByVal LigneDeTitre As Long, ByVal ColonneATrier As Long, ByVal ValeurDuFiltre As Double)
Dim DerniereColonne As Long
Dim DerniereLigne As Long
Dim AireATrier As Range
Dim AireColonne As Range
With FeuilleATrier
DerniereColonne = .Cells(LigneDeTitre, .Columns.Count).End(xlToLeft).Column
DerniereLigne = .Cells(.Rows.Count, ColonneATrier).End(xlUp).Row
If DerniereLigne > LigneDeTitre Then
Set AireATrier = .Range(.Cells(LigneDeTitre, 1), .Cells(DerniereLigne, DerniereColonne))
Set AireColonne = .Range(.Cells(LigneDeTitre, ColonneATrier), .Cells(DerniereLigne, ColonneATrier))
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=AireColonne, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange AireATrier
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With AireATrier
If .AutoFilter Then .AutoFilter
.AutoFilter Field:=ColonneATrier, Criteria1:=">=" & ValeurDuFiltre, Operator:=xlAnd
End With
Set AireColonne = Nothing
Set AireATrier = Nothing
End If
End With
End Sub |
Partager