Insertion de lignes vides dans Variable tableau VBA

Re bonjour :)

Pour des raisons d'affichage amélioré je voudrais éspacer mes lignes de variable tableau de lignes vides. Voici situation de départ et résultat souhaité:

A

1200

1200

4000

5000

Après déclaration et tri croissant que j'ai déjà fait, je voudrais que ça soit ainsi :

A

1200

1200

ligne vide

4000

ligne vide

5000

Voici le code que j'ai fait pour trier : les données commençent en A1 de la Feuil1 :

Sub déclarationTableau()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

    Dim Valeur As Integer
    Dim i As Integer
    Dim Cible As Variant
    Dim Cible2 As Variant
    Dim tableau(10, 2) 'Tableau de 10 x 2 "cases"

Dim ii As Integer

'Enregistrement des valeurs dans le tableau
For ii = 0 To 9
    tableau(ii, 0) = Range("A" & ii + 1)
    tableau(ii, 1) = Range("B" & ii + 1)

Range("E1") = tableau(5, 0)
Range("F1") = tableau(5, 1)
Next

Do 'tri croissant
        Valeur = 0

For i = 0 To UBound(tableau) - 2
            If tableau(i + 1, 0) < tableau(i, 0) Then
                Cible = tableau(i + 1, 0)
                tableau(i + 1, 0) = tableau(i, 0)
                tableau(i, 0) = Cible
                Cible2 = tableau(i + 1, 1)
                tableau(i + 1, 1) = tableau(i, 1)
                tableau(i, 1) = Cible2
                Valeur = 1
            End If
        Next i
    Loop While Valeur = 1

    'vérification du tri croissant
    Range("E3") = tableau(4, 0)
    Range("F3") = tableau(4, 1)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

pouvez vous m'aider svp ou bien me pister vers une solution ?

Merci beaucoup

Bonjour

A noter que ce tableau sera ensuite totalement figé : plus de tri, de filtre de synthèse... difficulté à formuler...

Pourquoi ne pas utiliser un traitement de texte ou un Etat Access ?

Merci Chris pour votre réponse.

En effet le résultat attendu est tel que j'ai décris. Après tri qui est fait, il ne manque plus que l'insertion de ligne vide entre les Ai différents.

avez vous une proposition svp ? Les données sont à partir de A1. Merci

Re

Une solution PowerQyuery avec une fonction concoctée par Jean-Eric

La requête peut être supprimée après si one shot

N'étant pas encore familiarisé avec les variables tableaux, j'ai réussi à codifier l'insertion de ligne vide sur une feuil Excel via ce code : toujours partant de A1 Feuil1

Dim sh As Worksheet
 Dim i As Integer

 Set sh = Sheets("Feuil1")
 i = 2
 While sh.Cells(i, 1) <> ""
  If sh.Cells(i - 1, 1) <> sh.Cells(i, 1) Then
    sh.Cells(i, 1).EntireRow.Insert Shift:=xlShiftDown
    i = i + 1
  End If
 i = i + 1
 Wend
End Sub

Quand je mets

tableau(i, 1).EntireRow.Insert Shift:=xlShiftDown

à la place de

sh.Cells(i, 1).EntireRow.Insert Shift:=xlShiftDown

Il m'indique une erreur et beug

Si quelqu'un peut m'aider à trouver le code applicable à la variable tableau déja déclarée je lui en serais très reconnaissant.

Merci

RE

Manifestement la solution PowerQuery simplissime ne t'intéresse pas... puisque tu ne réponds même pas...

Désolé Chris cest que je suis un peu surmené par le VBA.... J'OUBLIE DE repondre parfois, mais je préfère pour le moment me contenter de excel office car PowerQuery est payante d'après ce que j'ai compris.

Là où je travaille interdise les logiciels sans licences ...

Donc pouvez vous m'aider en me postant vers un code de variable tableau qui fait la même chose que mon code appliqué à la feuille :)

PQ est gratuit depuis ... et certainement dans votre version 2016.

inserter, on fait cela de bas en haut.

autre méthode

Sub Miroir()
     Dim SCA, aSCA, sOld, Lignes, Colonnes, a, aNouvelle
     t = Timer
     Set c = Range("B20:G31")     'la plage que vous voulez traiter
     a = c.Resize(c.Rows.Count + 1).Value     'lire dans une matrice, mais une ligne supplementaire !!!!
     For icol = 1 To UBound(a, 2)     'boucle les colonnes
          a(UBound(a), icol) = ""     'vider les éléments dans cette dernière ligne
     Next

     Set SCA = CreateObject("system.collections.arraylist")     'matrice trié
     For i = 1 To UBound(a) - 1     'boucle toutes les lignes sauf la dernière
          SCA.Add Left(a(i, 3) & WorksheetFunction.Rept(" ", 20), 20) & Format(i, "|000")     'créer un clé pour trier = la 3ième colonne (20 charactères pour être sur)  & "|"  & ligne dans la matrice
     Next
     SCA.Sort     'trier ce clé
     aSCA = SCA.toarray     '1D-matrice (base0 = commence avec index 0 au lieu de 1)

     'créer 1D-matrice pour les colonnes
     Colonnes = [column(1:1)]  '1D matrice sequence 1,2,3,4,5,..,+16.000
     ReDim Preserve Colonnes(1 To UBound(a, 2))     'réducer autant de chiffres qu'il y a des colonnes dans la plage, donc ici 6)

     'créer 2D-matrice pour les lignes
     sOld = ""     'valeur précédent de N° Compte
     For i = 0 To UBound(aSCA)     'boucle ce SCA
          sp = Split(aSCA(i), "|")     'séparer sur le charactère "|"
          If i > 1 And sp(0) <> sOld Then s = s & "," & UBound(a)     'ajouter le numéro de la dernière ligne (vide) quand le N°Compte change et ce n'est pas la première ligne
          sOld = sp(0)     'nouveau N° Compte
          s = s & "," & --sp(1)     'ajouter ligne qui correspond au prochaine ligne dans ce ordre croissant
     Next
     Lignes = Application.Transpose(Split(Mid(s, 2), ","))     'séparer s a partir du 2ième charactère et transponer pour recevoir une matrice 2D

     aNouvelle = Application.Index(a, Lignes, Colonnes)     'nouvelle matrice = ancienne matrice avec des lignes supplémentaires vides

     c.Offset(, c.Columns.Count + 2).Resize(UBound(aNouvelle), UBound(aNouvelle, 2)).Value = aNouvelle
     MsgBox "prêt en " & Format(Timer - t, "0.00\s")     'le temps est beaucoup plus petit que le marge d'erreur, pour mesurer !!!
End Sub

RE

BsAlv a écrit :

PQ est gratuit depuis ... et certainement dans votre version 2016.

Depuis 2010 en add on et totalement intégré dans Excel depuis 2016...

Et nombre d'utilisateurs sont passé du VBA à PowerQuery pour nombre de problématiques

Tu te dis au point dans Access et SQL mais préfère VBA à un requêteur... Bizarre...

OK cool dans ce cas je vais apprendre à l'utiliser certes ;)

Merci beaucoup pour votre appui

Bonsoir BsAlv, merci pour votre aide précieuse.

mais franchement je trouve que c'est trop difficile pour moi de comprendre le code proposé... y a pas de plus simple comme celui que j'ai appliqué sur la feuil1 directement. Je veux dire un équivalent applicable à une variable tableau ?

merci

dans une autre question, le problème était le temps, 15 secondes je crois, ce qui est exceptional long.

Avec ce methode on determine le sequence des lignes et des colonnes à recuperer et puis, dans une ligne on crée la nouvelle matrice. Tout se passe en mémoire, donc instantément. Une plage dynamique, c'est simplement changer l'addresse de cette plage c.

re,

Sub Tres_Tres_Simple()

     Set c = Range("B20:G31")     'la plage que vous voulez traiter, l'addresse est facile à modifier (=dynamique)

     With c.Offset(, c.Columns.Count + 2)     'plage 2 colonnes vers droite
          .Resize(.Rows.Count * 2).ClearContents     'effacer le double des lignes, précaution

          .Value = c.Value     'copier les valeur
          .Sort .Range("C1"), xlAscending, Header:=xlNo     'sorter la 3ième colonne
          For i = .Rows.Count - 1 To 1 Step -1     'boucle de bas en haut
               If .Cells(i, 3).Value <> .Cells(i + 1, 3).Value Then .Offset(i).Resize(1).Insert shift:=xlDown     'si N° Compte différent, insérer
          Next
MsgBox "nouvelle plage = " & .Address
     End With

End Sub

Mais où je devrai-je insérer le nom de la variable tableau "tableau" dans ce dernier code ?

re,

Sub Tres_Tres_Simple()
     Set c = Range("TBL_Cameleon")     'la plage que vous voulez traiter = le "databodyrange" d'un tableau quelque part dans ce fichier

     With Sheets("blad1").Range("F10").Resize(c.Rows.Count, c.Columns.Count)    'plage de destination = feuille "Blad1" cellule "F10" en haut et gauche
          .Resize(.Rows.Count * 2).ClearContents     'effacer le double des lignes, précaution

          .Value = c.Value     'copier les valeur
          .Sort .Range("C1"), xlAscending, Header:=xlNo     'sorter la 3ième colonne
          For i = .Rows.Count - 1 To 1 Step -1     'boucle de bas en haut
               If .Cells(i, 3).Value <> .Cells(i + 1, 3).Value Then .Offset(i).Resize(1).Insert shift:=xlDown     'si N° Compte différent, insérer
          Next
          MsgBox "les données se trouvent à " & .Address     'la plage avec les lignes inseré
     End With
End Sub

Cher BsAlv,

Voici en détail plus clair ce que j'ai :

le tableu matériel (sur Feuil1) est le suivant :

J'ai réussi à déclaré et trié un tableau virtuel/Variable tableau qui est vérifiéé et est la suivante :

Ce qui me reste à faire c'est finalement avoir une variable tableau comme ça : là ou deux valeurs de ligne sur la colonne 1 sont différentes les séparer par une ligne vide :

Pour la déclaration et tri j'ai utilisé ce code :

Sub déclarationTableau()

    Dim Valeur As Integer
    Dim i As Integer
    Dim Cible As Variant
    Dim Cible2 As Variant
    Dim tableau(10, 2) 'Tableau de 10 x 2 "cases"

Dim ii As Integer

'Enregistrement des valeurs dans le tableau
For ii = 0 To 9
    tableau(ii, 0) = Range("A" & ii + 1)
    tableau(ii, 1) = Range("B" & ii + 1)
Next

Do 'tri croissant
        Valeur = 0

For i = 0 To UBound(tableau) - 2
            If tableau(i + 1, 0) < tableau(i, 0) Then
                Cible = tableau(i + 1, 0)
                tableau(i + 1, 0) = tableau(i, 0)
                tableau(i, 0) = Cible
                Cible2 = tableau(i + 1, 1)
                tableau(i + 1, 1) = tableau(i, 1)
                tableau(i, 1) = Cible2
                Valeur = 1
            End If
        Next i
    Loop While Valeur = 1

    'vérification du tri croissant
    Range("E3") = tableau(4, 0)
    Range("F3") = tableau(4, 1)

End Sub

C'est possible de faire à la suite de ce code l'insertion de lignes vides car je ne maitrise pas bien les codes en mode variable Tableau.

Merci pour m'avoir accordé de votre temps précieux :)

vous demandez "simple" et faitez le tri en mémoire et puis insérer des lignes dans la feuille, alors pourquoi pas tout faire dans la feuille ?

Sub Tres_Tres_Simple()
     With Range("A6").CurrentRegion     'la plage autour de A6
          Set c = .Offset(1).Resize(.Rows.Count - 1, 2) 'la même plage 2 colonnes, sauf les entêtes
     End With

     With Range("D7").Resize(c.Rows.Count, c.Columns.Count)    'plage de destination = mêm feuille cellule "D7" en haut et gauche
          .Resize(.Rows.Count * 2).ClearContents     'effacer le double des lignes, précaution

          .Value = c.Value     'copier les valeur
          .Sort .Range("A1"), xlAscending, Header:=xlNo     'sorter sans entêtes, la 1ière colonne, croissant
          For i = .Rows.Count - 1 To 1 Step -1     'boucle de bas en haut
               If .Cells(i, 1).Value <> .Cells(i + 1, 1).Value Then .Offset(i).Resize(1).Insert shift:=xlDown     'si N° Compte différent, insérer
          Next
          MsgBox "les données se trouvent à " & .Address     'la plage avec les lignes inseré
     End With
End Sub

En effet quand je fais tous sur la feuille le userform qui prends les plage de la feuille met 15 secondes pour s'afficher. Avec la variable tableau ça ne prends que 3s. C'est pour ça que je souhaite vraiment terminer l'insertion des lignes vide dans le tableau mémoire et en tirer ma listbox du userform.

pouvez vous seulement me dire quel code faire pour l'insertion dans la variable tableau. Quand j'écris :

tableau(i, 1).Insert shift:=xlDown

il me mentionné une erreur en fait ! Merci

bonjour,

une autre proposition

Sub aargh()
    With Sheets("sheet1")
        dl = .Cells(Rows.Count, 1).End(xlUp).Row
        t = .Range("A1").Resize(dl, 2) 'données
        k = -1
        ReDim tr(1 To dl * 2, 1 To 2) 'tableau résultat final
        For i = 2 To dl 'on parcourt toutes les lignes de données
            If t(i, 1) <> t(i - 1, 1) Then k = k + 2 Else k = k + 1 ' critère ligne <> critère ligne-1 on insère une ligne dans résultat
            For j = 1 To 2
                tr(k, j) = t(i, j) 'on copie les données de la ligne i
            Next j
        Next i
        .Range("A2").Resize(k + 1, 2) = tr 'on copie le tableau dans la feuille !!!!! attention écrase les données existantes
    End With
End Sub

Je pense que personne ne m'a compris ... peut être aussi que je me suis mal expliqué !!!

En effet je veux que l'insertion se fait dans le tableau virtuel/mémoire/variable tableau et non pas sur la feuil1 ... donc pas visible.

Après en fin je viendrai affecter la listbox à ce tableau mémoire et là je vois le résultat...

:)