Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment insérer des lignes vides lorsque la valeur change dans Excel?

Supposons que vous ayez une plage de données et que vous souhaitiez maintenant insérer des lignes vides entre les données lorsque la valeur change, afin de pouvoir séparer les mêmes valeurs séquentielles dans une colonne comme les captures d'écran suivantes. Dans cet article, je vais vous parler de quelques astuces pour résoudre ce problème.

Insérer des lignes vides lorsque la valeur change avec la fonction Sous-total

Insérer des lignes vides lorsque la valeur change avec des colonnes d'assistance

Insérer des lignes vides lorsque la valeur change avec le code VBA

Insérez un nombre spécifique de lignes vides lorsque la valeur change avec une fonctionnalité puissante


Insérer des lignes vides lorsque la valeur change avec la fonction Sous-total

Avec la Sous-Total fonctionnalité, vous pouvez insérer des lignes vides entre les données lorsque la valeur change comme suit:

1. Sélectionnez la plage de données que vous souhaitez utiliser.

2. Cliquez Data > Sous-Total ouvrir le Sous-Total boîte de dialogue et dans la Sous-Total boîte de dialogue, veuillez faire les options suivantes:

1: Sélectionnez le nom de la colonne sur laquelle vous souhaitez insérer des lignes vides en fonction du changement de valeur sous A chaque changement dans la section;
2: Choisissez Compter du Utilisez la fonction la liste déroulante;
3: Cochez le nom de la colonne dans laquelle vous souhaitez insérer le sous-total dans le Ajouter un sous-total à zone de liste

3. Puis clique OK, les lignes de sous-total ont été insérées entre les différents produits, et les symboles de contour sont affichés à gauche du tableau, voir capture d'écran:

4. Et puis cliquez sur le numéro 2 en haut du symbole de plan pour afficher uniquement les lignes de sous-total.

5. Sélectionnez ensuite les données de plage de sous-total et appuyez sur Alt +; touches de raccourci pour sélectionner uniquement les lignes visibles, voir capture d'écran:

6. Après avoir sélectionné les lignes visibles uniquement, appuyez sur le bouton Supprimer sur le clavier, et toutes les lignes de sous-total ont été supprimées.

7. Cliquez ensuite sur n'importe quelle autre cellule et revenez en arrière Data > Dissocier > Effacer le contour pour supprimer les contours, voir capture d'écran:

8. Les symboles de contour ont été effacés à la fois et vous pouvez voir que des lignes vides ont été insérées entre les données lorsque la valeur change, voir capture d'écran:

9. Enfin, vous pouvez supprimer la colonne A selon vos besoins.


Insérer un saut de page, des lignes vides, une bordure inférieure ou une couleur de remplissage lorsque la valeur change rapidement

Si vous avez Kutools pour Excel's Distinguer les différences fonctionnalité, vous pouvez rapidement insérer un saut de page, des lignes vierges, une bordure inférieure ou une couleur de remplissage lorsque la valeur change selon vos besoins. Voir la démo ci-dessous.         Cliquez pour télécharger Kutools for Excel!


Insérer des lignes vides lorsque la valeur change avec des colonnes d'assistance

Avec les colonnes d'assistance, vous pouvez d'abord insérer une formule, puis appliquer le Rechercher et remplacer fonction, enfin, insérez les lignes vides entre les valeurs changeantes. Veuillez procéder comme suit:

1. Dans une cellule vide C3, veuillez entrer cette formule = A3 = A2, et dans la cellule D4 entrez cette formule = A4 = A3, voir capture d'écran:

2. Sélectionnez ensuite C3: D4 et faites glisser la poignée de recopie sur la plage à laquelle vous souhaitez appliquer ces formules, et vous obtiendrez Les véritables or Faux dans les cellules, voir capture d'écran:

3. Et puis appuyez sur Ctrl + F clés pour ouvrir le Rechercher et remplacer boîte de dialogue, dans la boîte de dialogue qui apparaît, entrez FAUX into the Trouvez ce que zone de texte sous Retrouvez des onglet, et cliquez Options : bouton pour développer cette boîte de dialogue et choisissez Nos valeurs du Regarder dans liste déroulante, voir capture d'écran:

4. Cliquez Trouver tout bouton, puis appuyez sur Ctrl + A pour sélectionner tous les résultats de recherche, toutes les cellules FALSE ont été sélectionnées en même temps, voir capture d'écran:

6. Fermer la Rechercher et remplacer boîte de dialogue, étape suivante, vous pouvez insérer des lignes vides en cliquant sur Accueil > insérer > Insérer des lignes de feuille, et des lignes vides ont été insérées dans les données lorsque la valeur change en fonction de la colonne A, voir les captures d'écran:

7. Enfin, vous pouvez supprimer les colonnes d'assistance C et D selon vos besoins.


Insérer des lignes vides lorsque la valeur change avec le code VBA

Si vous êtes fatigué d'utiliser les méthodes ci-dessus, voici un code qui peut également vous aider à insérer des lignes vides entre les valeurs modifiées à la fois.

1. Maintenez le ALT + F11 clés pour ouvrir le Fenêtre Microsoft Visual Basic pour Applications.

2. Cliquez insérer > Moduleet collez le code suivant dans le Fenêtre du module.

Code VBA: insérer des lignes vides lorsque la valeur change

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Puis appuyez F5 clé pour exécuter ce code, et une boîte de dialogue apparaîtra pour vous permettre de sélectionner une colonne de données que vous souhaitez insérer des lignes vides lorsque la valeur change sur laquelle vous vous basez, voir capture d'écran:

4. Et puis cliquez OK, les lignes vides ont été insérées entre les données lorsque la valeur change en fonction de la colonne A.


Insérez un nombre spécifique de lignes vides lorsque la valeur change avec une fonctionnalité puissante

Si vous êtes essayé avec les méthodes gênantes ci-dessus, ici, je vais vous présenter un outil utile, Kutools pour Excel's Distinguer les différences peut vous aider à insérer un saut de page, des lignes vides, une bordure inférieure ou une couleur de remplissage lorsque la valeur de la cellule change rapidement et facilement.

Conseils:Appliquer cette Distinguer les différences fonctionnalité, tout d'abord, vous devez télécharger le Kutools pour Excel, puis appliquez la fonction rapidement et facilement.

Après l'installation de Kutools pour Excel, veuillez faire comme ceci:

1. Cliquez Kutools > Format > Distinguer les différences, voir capture d'écran:

2. Dans le Distinguer les différences par colonne clé boîte de dialogue, veuillez effectuer les opérations suivantes:

  • Sélectionnez la plage de données que vous souhaitez utiliser, puis choisissez la colonne clé sur laquelle vous souhaitez insérer des lignes vides;
  • Puis vérifier Ligne vide Option de Options : section et entrez le nombre de lignes vides que vous souhaitez insérer.

3. Puis clique Ok bouton, et le nombre spécifique de lignes vides a été inséré dans les données si la valeur de la cellule change, voir les captures d'écran:

Cliquez pour télécharger Kutools pour Excel et un essai gratuit maintenant!


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Rob · 1 years ago
    Can the VBA method be adapted to ignore blank cells? I have a file which I need to insert rows in based on two different columns but when I run the macro on the second column I end up with three blank lines where the first macro run inserted rows.
    Or can it run on two columns at the same time?
  • To post as a guest, your comment is unpublished.
    Hassan · 3 years ago
    Hello,
    Is very usefull in case i need to insert 1 row, but if i need to insert 145 rows in every time the data change in spwcific column, how can i do it??
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hi, Hassan,
      To insert multiple blank rows when value changes in a specific column, you should apply the following VBA code:

      Note: In the below code, you should change the number 99 to your need, for example, when you insert 145 blank rows, you should change the number 99 to 144. Please try it, hope it can help you!

      Sub InsertRowsAtValueChange()
      Dim Rng As Range
      Dim WorkRng As Range
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      Application.ScreenUpdating = False
      For i = WorkRng.Rows.Count To 2 Step -1
      If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
      Range(WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Insert
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
      • To post as a guest, your comment is unpublished.
        DCalk · 3 years ago
        Very helpful. The code that skyyang shows above worked perfectly. Just make sure that the data doesn't already have spaces in it.

        I don't understand VBA, but I believe if you wanted to add more rows underneath data that already had the spacing, there should be a way to ignore spaces.

        Could a line be added to ignore or skip over blank lines? That might make this code more universal and repeatable if needed. Also a delete function that is similar to this may be useful so undo isn't necessary.
  • To post as a guest, your comment is unpublished.
    Suzi · 3 years ago
    RE: insert blank rows when value changes with vba code
    Is there a way that I can save the Range & not have to pick it every time I run it?
  • To post as a guest, your comment is unpublished.
    Eric F · 3 years ago
    The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
  • To post as a guest, your comment is unpublished.
    Steve · 4 years ago
    I've been using my own solution for some time.
    1. Insert a helper column into Column A
    2. In A2, type "if(B2=B1,A1,A1+1)"
    3. Copy that formula down to the last row
    4. Copy all the populated cells in column A and Paste Special (Values) over them
    5. Copy all the cells again and paste them into column A in the first unpopulated cell (e.g. if you have 104 rows of data plus a header row you would paste into cell A106)
    6. Click on Data and Remove Duplicates (only on the cells you just pasted in Step 5; not on all rows)
    7. Sort all of Column A
    8. Delete Column A

    Seems like a lot of steps but only takes a few seconds.
  • To post as a guest, your comment is unpublished.
    Madhur · 4 years ago
    [quote name="Hudson"]Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.[/quote]

    Please let me know how to insert more than 1 row.
  • To post as a guest, your comment is unpublished.
    Davis · 5 years ago
    Hi there,

    These are almost useful! The first method doesn't work for me because when I follow the steps explicitly, the the data that I delete in the subtotal panes deletes the entire columns that I've sorted.

    In the second method when I get to the step where I insert sheet rows, the rows are inserted ABOVE the FALSE cells which breaks up the data, but the last selection of every group is then added to the group below.

    Any advice???
  • To post as a guest, your comment is unpublished.
    Hudson · 5 years ago
    Hi all thank you!!

    its awesome , can you guys also let me how to insert 2 rows when the value changes in VBA or through excel.
  • To post as a guest, your comment is unpublished.
    Rafael · 6 years ago
    Where in the code would I need to modify to include more than one row, I need to add 10 after each break...

    Thanks
  • To post as a guest, your comment is unpublished.
    Ivo Tamm · 6 years ago
    Thank you very much for describing multiple solutions for the problem.
    I used the Visual Basic one and changed
    [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] with
    [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .Weight = xlMedium
    End With[/quote]
    This will draw lines between rows with changing values instead of empty lines.
  • To post as a guest, your comment is unpublished.
    Saul · 6 years ago
    Thank you so much, looked everywhere and this was the only solution that worked for me that was also in plain english.

    All the other Excel tutorial websites didn't break it down step-by-step they way you did, thank you very much.

    cheers : )
  • To post as a guest, your comment is unpublished.
    Abner · 6 years ago
    I have a column with the numbers 5,4,3,2 and 1 (a LOT of them) and I wanna separate the Fives from the others, like:

    5
    5
    5

    4
    3
    2
    1

    5
    5
    5
    5
    5

    4
    3
    2
    1

    The numbers are in desc order and there are always a four below a five,
    so I tried:

    Sub InsertRowsAtValueChange()
    'Update 20140716
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value = 5 And WorkRng.Cells(i - 1, 1).Value = 4 Then
    WorkRng.Cells(i, 1).EntireRow.Insert
    End If
    Next
    Application.ScreenUpdating = True
    End Sub

    But it didn't work. Can you help me?
    • To post as a guest, your comment is unpublished.
      Sam J Karmin · 7 months ago
      The formula is working great, however there are a few examples where it doesn't recognize