Ga naar hoofdinhoud

Hoe lege rijen invoegen wanneer de waarde verandert in Excel?

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

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

Voeg lege rijen in wanneer de waarde verandert met hulpkolommen

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 lege rijen in wanneer de waarde verandert met de functie Subtotaal

Met de Subtotaal functie, kunt u lege rijen tussen de gegevens invoegen wanneer de waarde verandert als volgt:

1. Selecteer het gegevensbereik dat u wilt gebruiken.

2. Klikken Data > Subtotaal om de te openen Subtotaal dialoogvenster en in het Subtotaal dialoog, voer dan de volgende opties uit:

1: Selecteer de kolomnaam waarvan u lege rijen wilt invoegen op basis van wanneer de waarde verandert onder Bij elke verandering in sectie;
2: Kies de Tellen van het Gebruiksfunctie keuzelijst;
3: Vink de kolomnaam aan waarin u het subtotaal wilt invoegen in het Voeg subtotaal toe aan keuzelijst

3. Dan klikken OK, de subtotaallijnen zijn tussen de verschillende producten ingevoegd en de overzichtssymbolen worden links van de tabel weergegeven, zie screenshot:

4. En klik vervolgens op het cijfer 2 bovenaan het overzichtssymbool om alleen de subtotaalregels weer te geven.

5. Selecteer vervolgens de gegevens van het subtotaalbereik 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 > Duidelijke omtrek zie screenshot om de contouren te verwijderen:

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

9. Eindelijk kunt u kolom A naar behoefte verwijderen.


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

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


Voeg lege rijen in wanneer de waarde verandert met hulpkolommen

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

1. Voer deze formule in een lege cel C3 in = A3 = A2, en voer in cel D4 deze formule in = A4 = A3, zie screenshot:

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

3. En druk vervolgens op Ctrl + F toetsen om de te openen Zoeken en vervangen dialoogvenster, voer in het uitgeklapte dialoogvenster Juist in de Zoeken naar tekstvak onder VIND DE PLEK DIE PERFECT VOOR JOU IS tab en klik Opties knop om dit dialoogvenster uit te vouwen, en kies Values van het Kijk in vervolgkeuzelijst, zie screenshot:

4. Klik Vind alle knop en druk vervolgens op Ctrl + A om alle resultaten van het vinden 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 op te klikken Home > Invoegen > Voeg bladrijen in, en er zijn lege rijen in de gegevens ingevoegd wanneer de waarde verandert op basis van kolom A, zie screenshots:

7. Ten slotte kunt u de hulpkolom C en D naar behoefte verwijderen.


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

Als u het gebruik van de bovenstaande methoden beu bent, is hier een code die u ook kan helpen om lege rijen in één keer tussen de gewijzigde waarden in te voegen.

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

2. Klikken Invoegen > Moduleen plak de volgende code in het 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 sleutel om deze code uit te voeren, en er verschijnt een promptvenster waarin u één kolomgegevens kunt selecteren die u lege rijen wilt invoegen wanneer de waarde verandert waarop u bent gebaseerd, zie screenshot:

4. En klik vervolgens op OK, zijn de lege rijen tussen de gegevens ingevoegd 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 u de bovenstaande lastige methoden probeert, zal ik hier een handig 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 gemakkelijk verandert.

Tips:Om dit toe te passen Onderscheid verschillen -functie, ten eerste moet u de Kutools for Excelen pas de functie vervolgens snel en gemakkelijk toe.

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

1. Klikken Kutools > Formaat > Onderscheid verschillen, zie screenshot:

2. In de Onderscheid verschillen per sleutelkolom dialoogvenster, voer dan 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;
  • Controleer dan Lege rij optie van de Opties sectie en voer het aantal lege rijen in dat u wilt invoegen.

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

Klik om Kutools voor Excel en nu gratis uit te proberen!

Beste Office-productiviteitstools

Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup    VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk    Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen    Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

kte tabblad 201905


Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, 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 vermindert honderden muisklikken voor u elke dag!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, Hassan,

This vba is amazing, whereas I need one more alternate code for insert single blank row after changes in sequencing numbers. Example:- In column having number series like 1, 2, 3, 5, 6, 9 & etc...
Need to add single blank row between 3 & 5 as well as 6 & 9.
Please can you with this.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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??
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
The VBA code worked 1st time and did exactly what I was trying to do. Thank you so much!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
[quote]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.By Hudson[/quote] Please let me know how to insert more than 1 row.
This comment was minimized by the moderator on the site
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???
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Where in the code would I need to modify to include more than one row, I need to add 10 after each break... Thanks
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations