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

[Erakmur]

Bonjour,

En tant normal, quand on fait un trie croissant ou décroissant sur une colonne, les cellules vides ne sont pas prises en compte mais quand elles sont calculées vides, elles sont prises en compte est apparaissent en tête de colonne quand le trie est de A à Z et cela me gène.

Dans le fichier joint, le filtre est en ligne 8 onglet IDF NORD. Je précise que le fait de décocher la case vide dans le filtre n'est pas la solution que je recherche puisque la mise en page change et que ce tableau à vocation à être dupliqué bcp de fois ce qui obligerai à décocher la case à chaque fois.

La solution doit marchée pour un trie croissant ou décroissant quelque soit la colonne de B8 à H8.

J'ai remarqué que si je remplaçais les "" par SI($B$9<$B$10;"0";"z") dans la colonne B cela donné un résultat juste mais uniquement si je ne fais pas les 2 même trie d'affiler.

Quelqu'un peut il m'aider ?

Cordialement

 

[JHA]

Bonjour à tous,

Un essai en pièce jointe

JHA

 

[Erakmur]

Je vois que tu as remplacé les "" par na() mais cela ne change rien. Les cellules vides apparaissent en tête de colonne quand je trie de Z à A ou du plus grand au plus petit.

 

[JHA]

Bonjour à tous,

Une autre piste par TCD

JHA

 

[Erakmur]

Je ne sais pas si tu l'a remarqué mais ce tableau est issus justement d'un TCD. J'ai d'ailleurs longuement travaillé à en extraire les donnés afin de sortir du TCD. C'est certainement pas pour y retourner.

 

[JHA]

Bonjour à tous,

Ah bon, tu avais un TCD?

Bon ben tant pis, attends d'autres réponses.

JHA

 

[chris]

Bonjour à tous
Coucou à JHA

Que ce soit ici ou sur les autres forums ou Erakmur poste, il fait preuve, comme on peut le constater, d’extrêmes politesse et amabilité envers ceux qui se décarcassent pour répondre à des questions qui souvent manquent de clarté en faisant allusion à des échanges faits ailleurs...

Ici je trouve le choix de récupération dans le TCD pour le moins exotique et vue l'ambiance des posts, je passe mon tour...

 

[Erakmur]

Bonjours,
Le TCD est dans l'onglet "DI par DR et par CF". j'ai finis par trouver la solution au problème. Merci pour votre temps.
Cordialement

 

[job75]

Bonjour le forum,

Ah bon c'est résolu comment Erakmur ?

Avec du VBA la rectification des tris est très simple, dans le code de la feuille "IDF NORD" :

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

Edit : si l'on coupe/insère les lignes "vides" la zone de filtrage est modifiée.

Donc je coupe/insère les lignes pleines.

J'ai créé le nom X =DECALER('IDF NORD'!$B$8;1;;NB.SI('IDF NORD'!$B:$B;"><")-2)

et modifié les définitions des noms Clotu Noncl Pourcen.

Fichier .xlsm joint.

Bonne journée.

 

[job75]

Re,

Notez qu'on peut se passer du tableau des colonnes J K L.

Dans ce fichier (2) voyez les nouvelles définitions des noms Clotu Noncl Pourcen.

A+

 

[Erakmur]

Bonjour job75,

Ton travail à l'air très intéressant (bien plus que ma solution). C'est donc une macro VBA + la modification des noms qui à permis cela ? Il faut maintenant que je vois si ce travail qui fonctionne pour IDF NORD fonctionne pour les 12 autres organisation. Le fichier que je t'ai envoyé n'est qu'un échantillon d'un fichier plus vaste. Il y a 13 onglets similaires à IDF NORD, un pour chaque organisation figurant en colonne C du TCD. Je suis chez moi, je ne possède pas ce fichier à la maison, je verrai cela lundi.

Je pense que tu l'as compris mais j'ai créé les noms clotu Noncl et Pourcen juste pour avoir une plage dynamique sur les graphiques et apparemment tu t'en sert aussi pour le problème de trie.

De plus, les colonnes J K L doivent figurer dans le fichier si on désire faire des trie dessus mais cela c'est un détail.

A+

 

[job75]

Re,

Une autre solution, plus sûre.

Elle consiste à copier sur la plage I8:I36 la formule =B8=""

Et à utiliser cette macro hyper simple :

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

Fichier (3).

Bonne nuit.

 

[job75]

Bonjour Erakmur, le forum,

Puisqu'on utilise VBA on peut supprimer les formules de recherche.

La macro dans le code de la feuille "DI par DR et par CF" :

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

Il y aura un bloc With/End With semblable pour chaque feuille de travail.

Le tableau s'ajuste automatiquement à la feuille source.

Fichier joint.

A+

 

[job75]

Re,

Pendant qu'on y est allons jusqu'au bout avec dans ThisWorkbook :

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

A+

 

[job75]

Re,

Pour terminer ici on utilise une seule feuille pour le filtrage :

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

Fichier (3), voyez la liste de validation en A8.

A+