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
|
Option Explicit
Sub TriDuFichier(ByVal FeuilleATrier As Worksheet, ByVal LigneDeTitre As Long, ByVal Colonne1ATrier As Long, ByVal Colonne2ATrier As Long)
Dim DerniereColonne As Long, DerniereLigne As Long
Dim AireATrier As Range, AireColonne1 As Range, AireColonne2 As Range
With FeuilleATrier
DerniereColonne = .Cells(LigneDeTitre, .Columns.Count).End(xlToLeft).Column
DerniereLigne = .Cells(.Rows.Count, Colonne1ATrier).End(xlUp).Row
If DerniereLigne > LigneDeTitre Then
Set AireATrier = .Range(.Cells(LigneDeTitre, 1), .Cells(DerniereLigne, DerniereColonne))
Set AireColonne1 = .Range(.Cells(LigneDeTitre, Colonne1ATrier), .Cells(DerniereLigne, Colonne1ATrier))
Set AireColonne2 = .Range(.Cells(LigneDeTitre, Colonne2ATrier), .Cells(DerniereLigne, Colonne2ATrier))
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=AireColonne1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=AireColonne2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange AireATrier
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set AireColonne2 = Nothing
Set AireColonne1 = Nothing
Set AireATrier = Nothing
End If
End With
End Sub
Sub TestTriDuFichier()
TriDuFichier Sheets("Feuil1"), 1, 14, 19
End Sub |
Partager