Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe voeg je lege rijen in als de waarde verandert in Excel?

Stel dat u een gegevensbereik hebt en nu wilt u lege rijen invoegen tussen de gegevens wanneer de waarde verandert, zodat u de opeenvolgende dezelfde waarden in één kolom kunt scheiden als de volgende getoonde schermafbeeldingen. In dit artikel zal ik enkele trucjes voor je bespreken om dit probleem op te lossen.

Voeg lege rijen in wanneer de waarde verandert met de subtotale functie

Voeg lege rijen in wanneer de waarde verandert met helperkolommen

Voeg lege rijen in wanneer de waarde verandert met VBA-code

Voeg een specifiek aantal lege rijen in wanneer de waarde verandert met een krachtige functie


Voeg pagina-einde, lege rijen, onderrand of vulkleur in wanneer de waarde snel verandert

Als je Kutools for Excel's Onderscheid verschillen kunt u snel pagina-einden, lege rijen, onderrand of opvulkleur invoegen wanneer de waarde naar behoefte wordt gewijzigd. Zie onderstaande demo. Klik om Kutools voor Excel te downloaden!


Voeg lege rijen in wanneer de waarde verandert met de subtotale functie

Met de Subtotaal functie, kunt u lege rijen invoegen tussen de gegevens wanneer de waarde wordt gewijzigd in de volgende stappen:

1. Selecteer het gegevensbereik dat u wilt gebruiken.

2. Klikken Data > Subtotaal om de te openen Subtotaal dialoogvenster en in de Subtotaal dialoog, doe de volgende opties:

1: Selecteer de kolomnaam waarvoor u lege rijen wilt invoegen op basis van wanneer de waarde onder verandert Bij elke verandering in sectie;
2: Kiezen Tellen van de Gebruiksfunctie keuzelijst;
3: Controleer de kolomnaam waar u het subtotaal in wilt invoegen Subtotaal toevoegen aan keuzelijst

3. Dan klikken OK, de subtotaalregels zijn ingevoegd tussen de verschillende producten en de contoursymbolen worden weergegeven aan de linkerkant van de tabel, zie screenshot:

4. Klik vervolgens op het cijfer 2 boven aan het contoursymbool om alleen de subtotaalregels weer te geven.

5. Selecteer vervolgens de subtotaalbereikgegevens en druk op Alt +; sneltoetsen om alleen de zichtbare rijen te selecteren, zie screenshot:

6. Nadat u alleen de zichtbare rijen hebt geselecteerd, drukt u op Verwijder toets op het toetsenbord en alle subtotaalrijen zijn verwijderd.

7. Klik vervolgens op een andere cel en ga terug Data > degroeperen > Overzicht wissen om de contouren te verwijderen, zie screenshot:

8. De overzichtssymbolen zijn in één keer gewist en u kunt zien dat er lege rijen tussen de gegevens zijn ingevoegd wanneer de waarde verandert, zie screenshot:

9. Eindelijk kunt u de kolom A verwijderen zoals u wilt.


Voeg lege rijen in wanneer de waarde verandert met helperkolommen

Met de hulpkolommen kunt u eerst de formule invoegen en vervolgens de Zoeken en vervangen functie, voeg tenslotte de lege rijen tussen de veranderende waarden in. Ga als volgt te werk:

1. Voer deze formule in een lege cel C3 in = A3 = A2en voer in cel D4 deze formule in = A4 = A3zie screenshot:

2. Selecteer vervolgens C3: D4 en sleep de vulgreep naar het bereik dat u wilt toepassen, met deze formules, en u krijgt Waar or Niet waar in de cellen, zie screenshot:

3. En druk vervolgens op Ctrl + F toetsen om de te openen Zoeken en vervangen dialoogvenster, voer in het uitgevouwen dialoogvenster in Juist in de Zoeken naar tekstvak onder Vind tab en klik opties om dit dialoogvenster uit te vouwen en kies waarden van de Kijk in drop-down lijst, zie screenshot:

4. Klik op Vind alle knop en druk vervolgens op Ctrl + A om alle resultaten van het zoeken te selecteren, zijn alle FALSE-cellen tegelijk geselecteerd, zie screenshot:

6. Sluit de Zoeken en vervangen dialoogvenster, volgende stap, kunt u lege rijen invoegen door te klikken Home > bijvoegsel > Velrij invoegenen lege rijen zijn ingevoegd in de gegevens wanneer de waarde verandert op basis van kolom A, zie screenshots:

7. Ten slotte kunt u de Help-kolom C en D verwijderen zoals u nodig hebt.


Voeg lege rijen in wanneer de waarde verandert met VBA-code

Als u het moe bent om de bovenstaande methoden te gebruiken, kunt u hier met een code ook lege rijen invoegen tussen de gewijzigde waarden tegelijk.

1. Houd de toets ingedrukt ALT + F11 toetsen om de te openen Microsoft Visual Basic for Applications-venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in de Module venster.

VBA-code: voeg lege rijen in wanneer de waarde verandert

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. Druk vervolgens op F5 toets om deze code uit te voeren, en er verschijnt een promptvenster waarin u één kolomgegevens kunt selecteren waarvoor u lege rijen wilt invoegen wanneer de waarde verandert waarop u zich baseert, zie screenshot:

4. En klik vervolgens op OK, de lege rijen zijn ingevoegd tussen gegevens wanneer de waarde verandert op basis van kolom A.


Voeg een specifiek aantal lege rijen in wanneer de waarde verandert met een krachtige functie

Als je wordt geprobeerd met de bovenstaande lastige methoden, zal ik hier een nuttig hulpmiddel introduceren, Kutools for Excel's Onderscheid verschillen kan u helpen bij het invoegen van pagina-einde, lege rijen, onderrand of opvulkleur wanneer de celwaarde snel en eenvoudig verandert.

Tips:Om dit toe te passen Onderscheid verschillen functie, ten eerste, zou u het moeten downloaden Kutools for Excelen pas de functie snel en eenvoudig toe.

Na het installeren van Kutools for Excel, doe alsjeblieft als volgt:

1. Klikken Kutools > Formaat > Onderscheid verschillenzie screenshot:

2. In de Onderscheid verschillen per sleutelkolom dialoogvenster, voer de volgende bewerkingen uit:

  • Selecteer het gegevensbereik dat u wilt gebruiken en kies vervolgens de sleutelkolom waarop u lege rijen wilt invoegen op basis van;
  • Vink dan aan Lege rij optie van de opties sectie en voer het aantal lege rijen in dat u wilt invoegen.

3. Dan klikken Ok en het specifieke aantal lege rijen zijn in de gegevens ingevoegd als de celwaarde verandert, zie screenshots:

Klik om Kutools voor Excel te downloaden en nu gratis uitproberen!


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen zonder gegevens te verliezen; Inhoud gesplitste cellen; Combineer dubbele rijen / kolommen... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Hassan · 10 months 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 · 10 months 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 · 10 months 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 · 11 months 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 · 1 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 · 1 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 · 2 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 · 2 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 · 3 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 · 3 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 · 3 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 · 4 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 · 4 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?