Au secours

Sub caractere() 'supprime le caractère "espace" devant et après les noms, prénoms et nationalités
                ' balaie les 3 colonnes D, E et F de toutes les feuilles
Dim a, i As Integer
Dim caractere As String
Dim chaine As String
Dim nombrecelule As Integer

caractere = " "

For Each feuille In Worksheets
    If feuille.Name <> "Athlètes" Then
    feuille.Select
Range("a2").Select
[B][COLOR=Red] nombrecellule = Range("a1").End(xlDown).Row[/COLOR][/B]
nomdefeuille = feuille.Name
    For a = 3 To 5
      For i = 0 To nombrecellule - 2
        [B][COLOR=Red]If ActiveCell.Offset(i, a) <> "" Then[/COLOR][/B]
        chaine = ActiveCell.Offset(i, a)
        If Mid(chaine, 1, 1) = caractere Then ActiveCell.Offset(i, a) = Mid(chaine, 2, Len(chaine))
        chaine = ActiveCell.Offset(i, a)
        If Mid(chaine, Len(chaine), 1) = caractere Then ActiveCell.Offset(i, a) = Mid(chaine, 1, Len(chaine) - 1)
        [B][COLOR=Red]End If[/COLOR][/B]
      Next i
    Next a

End If
Next feuille
End Sub

Sub test()
Dim colP As Integer
Dim colN As Integer
Dim colNat As Integer
Dim athletes As Collection
Set athletes = New Collection
For n = 1 To Sheets.Count
If Sheets(n).Name <> "Athlètes" Then
 For x = 1 To Sheets(n).Range("IV1").End(xlToLeft).Column
  If Sheets(n).Cells(1, x) = "Prénom" Then colP = x
  If Sheets(n).Cells(1, x) = "Nom" Then colN = x
  If Sheets(n).Cells(1, x) = "Code nationalité" Then colNat = x
 Next x
For m = 2 To Sheets(n).Range("A65536").End(xlUp).Row
  On Error Resume Next
    athletes.Add Trim(Sheets(n).Cells(m, colP)) & "_" & Trim(Sheets(n).Cells(m, colN)) & "_" & Trim(Sheets(n).Cells(m, colNat)), CStr(Trim(Sheets(n).Cells(m, colP)) & "_" & Trim(Sheets(n).Cells(m, colN)) & "_" & Trim(Sheets(n).Cells(m, colNat)))
  On Error GoTo 0
Next m
End If
Next n
For n = 1 To athletes.Count
 tableau = Split(athletes(n), "_")
 Sheets("Athlètes").Range("A" & n + 1) = tableau(0)
 Sheets("Athlètes").Range("B" & n + 1) = tableau(1)
 Sheets("Athlètes").Range("C" & n + 1) = tableau(2)
Next n
End Sub