XL 2010 Filtre de données et mise à jour

  Plage.Offset(1).Resize(Plage.Rows.Count - 1, Plage.Columns.Count).SpecialCells(xlCellTypeVisible).Copy Sheets("VARIANTES").[a4]

Option Explicit
Dim Tmp As String, Tmp1 As String, Plage
Private Sub ComboBox21_Change()
Sheets("VARIANTES").Range("a4:aa" & Rows.Count).Clear
If ComboBox21.Value = "" Then Exit Sub
With Sheets("ADAPTATION")
  Set Plage = .Range("a3:aa" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
  Plage.AutoFilter Field:=4, Criteria1:=ComboBox21.Value
  Plage.Offset(1).Resize(Plage.Rows.Count - 1, Plage.Columns.Count).SpecialCells(xlCellTypeVisible).Copy Sheets("VARIANTES").[a4]
End With
Plage.AutoFilter
End Sub
Private Sub Worksheet_Activate()
Dim F As Worksheet, i As Long, mondico, A(), temp
'auteur JB
Set F = Sheets("ADAPTATION")
Set mondico = CreateObject("Scripting.Dictionary")
A = F.Range("b3:b" & F.Cells(F.Rows.Count, "c").End(xlUp).Row)
For i = LBound(A) To UBound(A)
  If A(i, 1) <> "" Then mondico(A(i, 1)) = ""
Next i
'--avec tri
temp = mondico.keys
Call Tri(temp, LBound(temp), UBound(temp))
Sheets("VARIANTES").ComboBox21.List = temp
'Sheets("VARIANTES").ComboBox21.Value = ""
End Sub

Sub Tri(A, gauc, droi)  ' Quick sort
Dim Ref As String, g As Long, d As Long, temp
Ref = A((gauc + droi) \ 2)
g = gauc: d = droi
Do
  Do While A(g) < Ref: g = g + 1: Loop
  Do While Ref < A(d): d = d - 1: Loop
  If g <= d Then
    temp = A(g): A(g) = A(d): A(d) = temp
    g = g + 1: d = d - 1
  End If
Loop While g <= d
If g < droi Then Call Tri(A, g, droi)
If gauc < d Then Call Tri(A, gauc, d)
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Long, c
Cancel = True
With Sheets("VARIANTES")
  Set Plage = .Range("a4:aa" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
If Intersect(Target, Plage) Is Nothing Or [a4] = "" Then Exit Sub
With Sheets("ADAPTATION")
  Set Plage = .Range("a3:aa" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)
End With
Tmp = ""
LaligneTout = Target.Row
For i = 1 To 27: Tmp = Tmp & Cells(Target.Row, i): Next
For Each c In Plage.Rows
  Tmp1 = ""
  For i = 1 To 27: Tmp1 = Tmp1 & Sheets("ADAPTATION").Cells(c.Row, i): Next
  If Tmp = Tmp1 Then LaligneFiltre = c.Row
Next
UserForm1.Show
End Sub