Note: The other languages of the website are Google-translated. Back to English

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 de 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 tab en klik Opties knop om dit dialoogvenster uit te vouwen, en kies Values van de 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!


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren 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-2021 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functies Gratis proefperiode van 30 dagen. 60 dagen geld-terug-garantie.
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!
officetab onderkant
Heb je vragen? Stel ze hier. (15)
Nog geen beoordelingen. Beoordeel als eerste!
Deze opmerking is gemaakt door de moderator op de site
Ik heb een kolom met de nummers 5,4,3,2 en 1 (VEEL van hen) en ik wil de Vijven van de anderen scheiden, zoals: 5 5 5 4 3 2 1 5 5 5 5 5 4 3 2 1 De nummers staan ​​in volgorde en er zijn altijd vier onder een vijf, dus ik probeerde: Sub InsertRowsAtValueChange() 'Update 20140716 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Stel 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). Waarde = 5 En WorkRng.Cells(i - 1, 1).Value = 4 Dan WorkRng.Cells(i, 1).EntireRow.Insert End If Next Application.ScreenUpdating = True End Sub Maar het werkte niet. Kunt u mij helpen?
Deze opmerking is gemaakt door de moderator op de site
De formule werkt prima, maar er zijn een paar voorbeelden waarin het niet wordt herkend
Deze opmerking is gemaakt door de moderator op de site
Heel erg bedankt, overal gekeken en dit was de enige oplossing die voor mij werkte en die ook in gewoon Engels was. Alle andere Excel-zelfstudiewebsites hebben het niet stap voor stap opgesplitst zoals u deed, heel erg bedankt. proost : )
Deze opmerking is gemaakt door de moderator op de site
Hartelijk dank voor het beschrijven van meerdere oplossingen voor het probleem. Ik gebruikte de Visual Basic en veranderde [quote]WorkRng.Cells(i, 1).EntireRow.Insert[/quote] met [quote]With WorkRng.Cells(i, 1).EntireRow.Borders(xlEdgeTop) .LineStyle = xlContinuous .ColorIndex = xlAutomatic .TintAndShade = 0 .Weight = xlMedium End With[/quote] Dit zal lijnen trekken tussen rijen met veranderende waarden in plaats van lege regels.
Deze opmerking is gemaakt door de moderator op de site
Waar ik in de code zou moeten wijzigen om meer dan één rij op te nemen, moet ik na elke pauze 10 toevoegen ... Bedankt
Deze opmerking is gemaakt door de moderator op de site
Hallo allemaal bedankt!! het is geweldig, kunnen jullie me ook laten weten hoe ik 2 rijen invoeg wanneer de waarde verandert in VBA of via Excel.
Deze opmerking is gemaakt door de moderator op de site
Hallo daar, Deze zijn bijna nuttig! De eerste methode werkt niet voor mij omdat wanneer ik de stappen expliciet volg, de gegevens die ik verwijder in de subtotaalpanelen de volledige kolommen verwijderen die ik heb gesorteerd. In de tweede methode, wanneer ik bij de stap kom waar ik bladrijen invoeg, worden de rijen BOVEN de ONWAAR cellen ingevoegd die de gegevens opsplitsen, maar de laatste selectie van elke groep wordt vervolgens toegevoegd aan de groep hieronder. Enig advies???
Deze opmerking is gemaakt door de moderator op de site
[quote]Hallo allemaal bedankt!! het is geweldig, kunnen jullie me ook laten weten hoe ik 2 rijen invoeg wanneer de waarde verandert in VBA of via Excel.Door Hudson[/quote] Laat me weten hoe ik meer dan 1 rij kan invoegen.
Deze opmerking is gemaakt door de moderator op de site
Ik gebruik al een tijdje mijn eigen oplossing.
1. Voeg een hulpkolom in kolom A . in
2. Typ in A2 "if(B2=B1,A1,A1+1)"
3. Kopieer die formule naar de laatste rij
4. Kopieer alle bevolkte cellen in kolom A en plak speciaal (waarden) eroverheen
5. Kopieer alle cellen opnieuw en plak ze in kolom A in de eerste niet-bevolkte cel (bijvoorbeeld als u 104 rijen met gegevens hebt plus een kopregel die u in cel A106 zou plakken)
6. Klik op Gegevens en verwijder duplicaten (alleen op de cellen die u zojuist in stap 5 hebt geplakt; niet op alle rijen)
7. Sorteer de hele kolom A
8. Verwijder kolom A

Het lijken veel stappen, maar het duurt maar een paar seconden.
Deze opmerking is gemaakt door de moderator op de site
De VBA-code werkte de eerste keer en deed precies wat ik probeerde te doen. Hartelijk bedankt!
Deze opmerking is gemaakt door de moderator op de site
RE: voeg lege rijen in wanneer de waarde verandert met vba-code
Is er een manier waarop ik het bereik kan opslaan en het niet elke keer hoef te kiezen als ik het start?
Deze opmerking is gemaakt door de moderator op de site
Hallo,
Is erg handig voor het geval ik 1 rij moet invoegen, maar als ik 145 rijen moet invoegen telkens wanneer de gegevens in de specifieke kolom veranderen, hoe kan ik dat dan doen??
Deze opmerking is gemaakt door de moderator op de site
Hallo, Hassan,
Als u meerdere lege rijen wilt invoegen wanneer de waarde in een specifieke kolom verandert, moet u de volgende VBA-code toepassen:

Opmerking: in de onderstaande code moet u het nummer 99 naar uw behoefte wijzigen, bijvoorbeeld wanneer u 145 lege rijen invoegt, moet u het nummer 99 wijzigen in 144. Probeer het alstublieft, ik hoop dat het u kan helpen!

Sub InsertRowsAtValueChange()
Dim Rng als bereik
Dim WorkRng als bereik
On Error Resume Next
xTitleId = "KutoolsforExcel"
Stel WorkRng = Application.Selection in
Set WorkRng = Application.InputBox("Bereik", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Voor i = WorkRng.Rows.Count To 2 Stap -1
Als WorkRng.Cells(i, 1).Waarde <> WorkRng.Cells(i - 1, 1).Waarde Dan
Bereik (WorkRng.Cells(i, 1).EntireRow, WorkRng.Cells(i + 99, 1).EntireRow).Invoegen
End If
Volgende
Application.ScreenUpdating = True
End Sub
Deze opmerking is gemaakt door de moderator op de site
Erg behulpzaam. De code die Skyyang hierboven laat zien werkte perfect. Zorg ervoor dat de gegevens nog geen spaties bevatten.

Ik begrijp VBA niet, maar ik geloof dat als je meer rijen wilt toevoegen onder gegevens die al de spaties hadden, er een manier zou moeten zijn om spaties te negeren.

Kan een regel worden toegevoegd om lege regels te negeren of over te slaan? Dat kan deze code indien nodig universeler en herhaalbaar maken. Ook een verwijderfunctie die hierop lijkt kan handig zijn, dus ongedaan maken is niet nodig.
Deze opmerking is gemaakt door de moderator op de site
Kan de VBA-methode worden aangepast om lege cellen te negeren? Ik heb een bestand waarin ik rijen moet invoegen op basis van twee verschillende kolommen, maar wanneer ik de macro in de tweede kolom uitvoer, krijg ik drie lege regels waar de eerste macro-run ingevoegde rijen bevat.
Of kan het tegelijkertijd op twee kolommen draaien?
Er zijn nog geen reacties geplaatst
Laat uw commentaar
Posten als gast
×
Beoordeel dit bericht:
0   Personages
Voorgestelde locaties

Volg ons

Copyright © 2009 - www.extendoffice.com. | Alle rechten voorbehouden. Aangedreven door ExtendOffice. | Sitemap
Microsoft en het Office-logo zijn handelsmerken of gedeponeerde handelsmerken van Microsoft Corporation in de Verenigde Staten en / of andere landen.
Beschermd door Sectigo SSL