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
| Option Explicit
Public DetectionColonnes As String
Sub Filtre()
Dim LigneDeTitre As Long
Dim ColPoleTrait As Integer
With ActiveSheet
LigneDeTitre = 1
DetectionColonnes = "Absence colonnes :" & Chr(10)
ColPoleTrait = ColonneFeuille(ActiveSheet, LigneDeTitre, "PoleTrait")
If DetectionColonnes = "Absence colonnes :" & Chr(10) Then
.Range("$A$1:$AP$9087").AutoFilter Field:=ColPoleTrait, Criteria1:=Array( _
"DE-VA POLE AMERIQUES", "DE-VA POLE FRANCE", "DE-VA POLE ROUMANIE", _
"DE-VB POLE AMERIQUES", "DE-VB POLE COREE", "DE-VB POLE FRANCE", _
"DE-VB POLE ROUMANIE", "DE-VD POLE AMERIQUES", "DE-VD POLE COREE", _
"DE-VD POLE FRANCE", "DE-VD POLE ROUMANIE", "DE-VE POLE AMERIQUES", _
"DE-VE POLE COREE", "DE-VE POLE FRANCE", "DE-VE POLE ROUMANIE"), Operator:= _
xlFilterValues
Else
MsgBox DetectionColonnes, vbCritical
End If
End With
End Sub
Function ColonneFeuille(ByVal FeuilleTitre As Worksheet, ByVal LigneTitre As Long, ByVal TitreRecherche As String) As Long
Dim NbColonnes As Long
Dim Cellule As Range
Dim Aire As Range
With FeuilleTitre
ColonneFeuille = 0
NbColonnes = .Cells(LigneTitre, .Columns.Count).End(xlToLeft).Column
Set Aire = .Range(.Cells(LigneTitre, 1), .Cells(LigneTitre, NbColonnes))
For Each Cellule In Aire
Select Case Mid(Cellule.Value, 1, Len(TitreRecherche))
Case TitreRecherche
ColonneFeuille = Cellule.Column
Exit For
End Select
Next
If ColonneFeuille = 0 Then DetectionColonnes = DetectionColonnes & Chr(10) & TitreRecherche
Set Aire = Nothing
End With
End Function |
Partager