XL 2010 [Résolu] Pas prendre en compte cellules calculées vide quand trie

Private Sub Worksheet_Calculate()
With [A7].CurrentRegion
  If .Cells(3, 2) <> "" Then Exit Sub
  Application.EnableEvents = False
  .Rows(.Rows.Count - [X].Count + 1).Resize([X].Count).EntireRow.Cut
  .Cells(3, 1).Insert
  Application.EnableEvents = True
End With
End Sub

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
[A7].CurrentRegion.EntireRow.Sort [I7], xlAscending, Header:=xlYes 'tri
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Variant, j As Variant
With Sheets("IDF Nord")
  If .FilterMode Then .ShowAllData 'si la feuille est filtrée
  .Rows("9:" & .Rows.Count) = "" 'RAZ
  i = Application.Match(.[A8], [A:A], 0)
  If IsError(i) Then Exit Sub
  j = Application.Match("*", Range("A" & i + 1 & ":A" & Rows.Count), 0)
  If IsError(j) Then j = [B:B].Find("", Cells(i, 2), xlValues).Row - i
  .[A9].Resize(j, 7) = Cells(i, 2).Resize(j, 7).Value
  .[I8:K8].AutoFill .[I8:K8].Resize(j + 1), xlFillValues
End With
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim F As Worksheet, a, n%, i As Variant, j As Variant
Set F = Sheets("DI par DR et par CF") 'feuille source
a = Array("IDF Nord", "BPL") 'liste des noms des feuilles à traiter
Application.EnableEvents = False
On Error Resume Next 'sécurité
If Sh.Name = F.Name Then
  Application.ScreenUpdating = False
  For n = 0 To UBound(a)
    With Sheets(a(n))
      .ShowAllData 'si la feuille est filtrée
      .Rows("9:" & .Rows.Count) = "" 'RAZ
      i = Application.Match(.[A8], Sh.[A:A], 0)
      If IsNumeric(i) Then
        j = Application.Match("*", Sh.Range("A" & i + 1 & ":A" & Sh.Rows.Count), 0)
        If IsError(j) Then j = Sh.[B:B].Find("", Sh.Cells(i, 2), xlValues).Row - i
        .[A9].Resize(j, 7) = Sh.Cells(i, 2).Resize(j, 7).Value
        .[I8:K8].AutoFill .[I8:K8].Resize(j + 1), xlFillValues
      End If
    End With
  Next
ElseIf IsNumeric(Application.Match(Sh.Name, a, 0)) Then
  If Not Intersect(Target, [A8]) Is Nothing Then Workbook_SheetChange F, F.[A1] 'MAJ
  If Not Intersect(Target, Sh.Rows("9:" & Sh.Rows.Count)) Is Nothing Then Application.Undo 'annule la modification manuelle
End If
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim F As Worksheet, i As Variant, j As Variant
Set F = Sheets("DI par DR et par CF") 'feuille source
Application.EnableEvents = False
On Error Resume Next 'sécurité
If Sh.Name = F.Name Then
  Application.ScreenUpdating = False
  With Sheets("Filtrage")
    .ShowAllData 'si la feuille est filtrée
    .Rows("9:" & .Rows.Count) = "" 'RAZ
    i = Application.Match(.[A8], Sh.[A:A], 0)
    If IsNumeric(i) Then
      j = Application.Match("*", Sh.Range("A" & i + 1 & ":A" & Sh.Rows.Count), 0)
      If IsError(j) Then j = Sh.[B:B].Find("", Sh.Cells(i, 2), xlValues).Row - i
      .[A9].Resize(j, 7) = Sh.Cells(i, 2).Resize(j, 7).Value
      .[I8:K8].AutoFill .[I8:K8].Resize(j + 1), xlFillValues
    End If
  End With
ElseIf Sh.Name = "Filtrage" Then
  If Not Intersect(Target, Sh.[A8]) Is Nothing Then Workbook_SheetChange F, F.[A1] 'MAJ
  If Not Intersect(Target, Sh.Rows("9:" & Sh.Rows.Count)) Is Nothing Then Application.Undo 'annule la modification manuelle
End If
Application.EnableEvents = True
End Sub