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
|
'Exemple d'utilisation
' Sort and Filter by production site to speed-up the process
Call Remove_Autofilter(ActiveWorkbook, DataRng.Worksheet.Name)
Set DataRng = DataRng.CurrentRegion
' Filter by prodsite and not-affected lots
With DataRng
.AutoFilter 'Set the autofilter On
.AutoFilter Field:=ColF, Criteria1:=ProdSite
.AutoFilter Field:=ColFlag, Criteria1:="="
End With
'Sort
With DataRng
.Sort _
key1:=DataRng.Cells(ColFlag), Order1:=xlAscending, _
key2:=DataRng.Cells(6), Order2:=xlAscending, _
key3:=DataRng.Cells(1), Order3:=xlAscending, _
Header:=xlYes, Orientation:=xlSortColumns
'
End With
' Exemple remove duplicate
InpRng.RemoveDuplicates Columns:=(RngColAR), Header:=RngAsHeader
Sub Remove_Autofilter(Wbk As Workbook, WshN As String)
Dim Wsh As Worksheet
If IsMissing(Wbk) Then Set Wbk = ActiveWorkbook
Set Wsh = Wbk.Worksheets(WshN)
If Wsh.AutoFilterMode = True Then Wsh.AutoFilterMode = False
End Sub
Sub Set_Autofilter(Wbk As Workbook, WshN As String, RngAd As String)
Dim Wsh As Worksheet
Dim AFiltRng As Range
If IsMissing(Wbk) Then Set Wbk = ActiveWorkbook
Set Wsh = Wbk.Worksheets(WshN)
Set AFiltRng = Wsh.Range(RngAd).CurrentRegion
If Is_Rng_Defined(Rng:=AFiltRng) = True Then AFiltRng.AutoFilter
End Sub |
Partager