Public Sub RefreshData(feuille As Worksheet, colonne As String)
Dim mem1 As Long, mem2 As Long, mem3 As Long
'mémoriser/désactiver les options d'excel
mem1 = Application.Calculation: Application.Calculation = xlCalculationManual
mem2 = Application.EnableEvents: Application.EnableEvents = False
mem3 = Application.ScreenUpdating: Application.ScreenUpdating = False
'exécuter la macro
On Error Resume Next
RefreshDataInt feuille, colonne
On Error GoTo 0
'rétablir les options d'excel
Application.Calculation = mem1
Application.EnableEvents = mem2
Application.ScreenUpdating = mem3
End Sub
Private Sub RefreshDataInt(feuille As Worksheet, colonne As String)
Dim tabVal() As Variant, i As Long, j As Long, laCell As Range, tmp As Variant, ligneE As Long
'effacer le contenu de la feuille
feuille.Range("A1").CurrentRegion.Offset(1, 0).EntireRow.Clear
ReDim tabVal(1 To 2, 1 To 1)
ligneE = 1
With ThisWorkbook.Sheets("Individus")
'boucler sur toutes les données de la colonne A (Naissances) de la feuille Individus
For Each laCell In .Range(colonne & "2:" & colonne & .Range(colonne & .Rows.Count).End(xlUp).Row)
'si la cellule n'est pas vide
If laCell.Text <> "" Then
'redimensionner le tableau
i = i + 1
ReDim Preserve tabVal(1 To 2, 1 To i)
'ajouter la valeur de la cellule ainsi que sa ligne au tableau
tabVal(1, i) = laCell.Text
tabVal(2, i) = laCell.Row
End If
Next laCell
'trier alphabétiquement le tableau
For i = LBound(tabVal, 2) To UBound(tabVal, 2) - 1
For j = i + 1 To UBound(tabVal, 2)
If tabVal(1, j) < tabVal(1, i) Then
tmp = tabVal(1, j)
tabVal(1, j) = tabVal(1, i)
tabVal(1, i) = tmp
tmp = tabVal(2, j)
tabVal(2, j) = tabVal(2, i)
tabVal(2, i) = tmp
End If
Next j
Next i
'boucler sur chaque élément du tableau et rapatrier les données sur la feuille
For i = LBound(tabVal, 2) To UBound(tabVal, 2)
ligneE = ligneE + 1
feuille.Range("A" & ligneE).Value = tabVal(1, i)
feuille.Range("B" & ligneE).Value = .Range("D" & tabVal(2, i)).Value
feuille.Range("C" & ligneE).Value = .Range("E" & tabVal(2, i)).Value
feuille.Range("D" & ligneE).Value = .Range("F" & tabVal(2, i)).Value
feuille.Range("E" & ligneE).Value = .Range("G" & tabVal(2, i)).Value
feuille.Range("F" & ligneE).Value = .Range("H" & tabVal(2, i)).Value
feuille.Range("G" & ligneE).Value = .Range("I" & tabVal(2, i)).Value
feuille.Range("H" & ligneE).Value = .Range("J" & tabVal(2, i)).Value
feuille.Range("I" & ligneE).Value = .Range("K" & tabVal(2, i)).Value
feuille.Range("J" & ligneE).Value = .Range("L" & tabVal(2, i)).Value
Next i
End With
End Sub