Sub maj()
nbonglet = ThisWorkbook.Sheets.Count
balise = 2
Worksheets("Synthèse").Range("A2:P150000").Clear
For onglet = 3 To nbonglet
    ligne = 2
    nbligne = Worksheets(onglet).UsedRange.Rows.Count
    Worksheets("Synthèse").Range("A" & balise & ":F" & balise + nbligne).Value = Worksheets(onglet).Range("A" & ligne & ":F" & nbligne).Value
    balise = balise + nbligne + 1
Next

' supprime la mise en forme
 Dim wrksht As Worksheet
 Dim objListObj As ListObject
 
 Set wrksht = ActiveWorkbook.Worksheets("Synthèse")
 If wrksht.ListObjects.Count > 0 Then
    Set objListObj = wrksht.ListObjects(1)
    objListObj.Unlist
End If

' supprime les lignes vides
With Worksheets("Synthèse").Select
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Columns(3).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Columns(4).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Columns(5).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
On Error Resume Next
Columns(6).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With

Dim zone As Range
Dim der_ligne As Long

Set zone = Range("C1").CurrentRegion
der_ligne = zone.Rows(zone.Rows.Count).Row

' mettre ligne en vert si #N/A
' For i = der_ligne To 1 Step -1
    ' If Application.IsNA(Cells(i, 2).Value) Then
        ' Range(Rows(i), Rows(i)).Interior.Color = RGB(50, 200, 100)
    ' End If
' Next

' supprime ligne si #N/A
For i = der_ligne To 1 Step -1
    If Application.IsNA(Cells(i, 2).Value) Then
        Range(Rows(i), Rows(i)).Delete
    End If
Next


Dim Plage As Range
Dim LaColonne As Integer
Dim LeMot As String
Dim NomFeuille As String
    NomFeuille = "Synthèse"
    LeMot = "*??*"
    LaColonne = 5
 
    With ThisWorkbook.Worksheets(NomFeuille)
        ' plage des données
        Set Plage = .Cells(2, 1).Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count)
 
        With .Range("A1")
            ' retrait des filtres s'il y en a
            .AutoFilter
            ' application du filtre
            .AutoFilter LaColonne, LeMot
            On Error Resume Next
                ' tentative de suppression des résultats
                Plage.SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
            ' suppression des filtres
            .AutoFilter
        End With
    End With

End Sub