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

or

Hoe rij automatisch in te voegen op basis van de celwaarde in Excel?

doc-insert-rij-basis-on-value-1
Stel dat u een gegevensbereik hebt en als u automatisch lege rijen boven of onder een bepaalde waarde in Excel wilt invoegen, voegt u bijvoorbeeld automatisch rijen onder nulwaarde in, zoals onderstaand screenshot. In Excel is er geen directe manier om deze taak op te lossen, maar ik kan een macrocode voor u invoeren om automatisch rijen in te voegen op basis van een bepaalde waarde in Excel.
Voeg onderstaande rij in op basis van de celwaarde met VBA

Voeg bovenstaande rij in op basis van de celwaarde met Kutools voor Excel goed idee3

Selecteer Cellen / rijen / kolommen met een of twee criteria in Excel

De geselecteerde specifieke cellen van Kutools voor Excel kunnen snel alle cellen of rijen of kolommen in een bereik selecteren op basis van één criterium of twee criteria. Klik voor gratis proefperiode van 60 dagen!
doc selecteer specifieke cel 1
Kutools voor Excel: met meer dan 300 handige Excel-invoegtoepassingen, gratis om zonder beperkingen in 60-dagen te proberen.

Ga als volgt te werk als u een rij wilt invoegen op basis van de celwaarde door VBA uit te voeren:

formule Nooit meer zorgen maken over lange lange formules in Excel! Kutools for Excel's Auto-tekst kan alle formules aan een groep toevoegen
als autotekst, en bevrijd je hersenen! Klik hier om Auto Text te kennen Klik hier om een ​​gratis proefversie te krijgen

1. druk op Alt + F11 toetsen tegelijk, en a Microsoft Visual Basic voor toepassingen venster verschijnt.

2. klikken bijvoegsel > module, plak dan onder VBA-code in de popping module venster.

VBA: voeg onderstaande rij in op basis van de celwaarde.

Sub BlankLine()
	'Updateby20150203
	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)
	Set WorkRng                = WorkRng.Columns(1)
	xLastRow                   = WorkRng.Rows.Count
	Application.ScreenUpdating = False
	For xRowIndex = xLastRow To 1 Step - 1
		Set Rng                   = WorkRng.Range("A" & xRowIndex)
		If Rng.Value = "0" Then
			Rng.Offset(1, 0).EntireRow.Insert Shift: = xlDown
		End If
	Next
	Application.ScreenUpdating = True
End Sub

3. klikken F5 toets of de lopen knop, een dialoogvenster verschijnt en selecteer de kolom bevat nul. Zie screenshot:
doc-insert-rij-basis-on-value-2

4. klikken OK. Vervolgens worden lege rijen onder nulwaarde ingevoegd.
doc-insert-rij-basis-on-value-3

Tip:

1. Als u rijen wilt invoegen op basis van een andere waarde, kunt u deze wijzigen 0 naar elke gewenste waarde in de VBA: Als Rng.Value = "0" dan.

2. Als u rijen boven nul of een andere waarde wilt invoegen, kunt u de onderstaande vba-code gebruiken.

VBA: rij boven nulwaarde invoegen:

Sub BlankLine()
	'Updateby20150203
	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)
	Set WorkRng                = WorkRng.Columns(1)
	xLastRow                   = WorkRng.Rows.Count
	Application.ScreenUpdating = False
	For xRowIndex = xLastRow To 1 Step - 1
		Set Rng                   = WorkRng.Range("A" & xRowIndex)
		If Rng.Value = "0" Then
			Rng.EntireRow.Insert Shift: = xlDown
		End If
	Next
	Application.ScreenUpdating = True
End Sub

doc-insert-rij-basis-on-value-4


Als u niet bekend bent met VBA, kunt u het proberen Kutools for Excel's Selecteer specifieke cellen hulpprogramma en voer vervolgens rijen hierboven in.

Kutools for Excel, met meer dan 300 handige functies, maakt uw taken gemakkelijker.

Na het installeren van Kutools voor Excel, doe het als volgt:(Gratis download nu Kutools voor Excel!)

1. Selecteer de lijst waarvan u de specifieke cellen wilt achterhalen en klik op Kutools > kiezen > Selecteer specifieke cellen. Zie screenshot:
rij invoegen op basis van waarde 9

2. Controleer in het popping-dialoogvenster Hele rij optie en ga vervolgens naar selecteren Is gelijk aan vanaf Specifiek type lijst en voer vervolgens de waarde in die u in het juiste tekstvak wilt vinden. Zie screenshot:
rij invoegen op basis van waarde 6

3. klikken Oken er verschijnt een dialoogvenster om u het aantal geselecteerde rijen te herinneren, sluit het gewoon.

4. Plaats de cursor op een geselecteerde rij en klik met de rechtermuisknop om te selecteren bijvoegsel van contextmenu. Zie screenshot:
rij invoegen op basis van waarde 7

Nu worden de rijen hierboven ingevoegd op basis van een specifieke waarde.
rij invoegen op basis van waarde 8


Relatieve artikelen:

Met tabbladen bladeren en bewerken van meerdere Excel-werkmappen / Word-documenten als Firefox, Chrome, Internet Verkennen 10!

U kunt er bekend mee zijn om meerdere webpagina's in Firefox / Chrome / IE te bekijken en tussen deze pagina's te schakelen door eenvoudig op overeenkomstige tabbladen te klikken. Hier ondersteunt de Office-tab een vergelijkbare verwerking, waarmee u door meerdere Excel-werkmappen of Word-documenten kunt bladeren in een Excel-venster of in een Word-venster en eenvoudig kunt schakelen tussen deze werkbladen door op hun tabbladen te klikken. Klik voor gratis 45-dag proef van Office Tab!

iet uitblinken

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

  • 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 en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... 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 ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • 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. Werkt met Office 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in bedrijf. 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.
    safa · 3 months ago
    Hello, this is very helpful. What if I wanted to add two lines below and I wanted to more values. For instance, I want to add two lines after value 26/04/2019 and then two lines after 03/04/2019, and list goes on. How do I keep adding to the vba? Sorry im still a beginner. Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Gina · 8 months ago
    Hi, Thanks for this, however I have another scenario where I need to insert a cingle cell under the value that is not zero. Appreciate any suggestion.
  • To post as a guest, your comment is unpublished.
    Louis · 11 months ago
    Hi i am trying to use this code to enter a row when a the first 4 digits in a cell changes (if thats even possible)

    for example,
    2222A
    2222B
    2223K


    the line will be inserted after 2222B as the 3rd number is a 3 and not a 2

    Thanks guys!!
  • To post as a guest, your comment is unpublished.
    Hi · 1 years ago
    hi I just wanna ask how to add row if the codition is that add row should be done when a cell has a data already (It is for a excel workbook with a lot of sheets :) Thanks!
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      maybe this vba code can help you. It will add rows if above row is not empty

      Sub helping()
      Dim count As Long
      For count = ActiveSheet.UsedRange.Rows.count To 1 Step -1
      If Information.IsEmpty(Cells(count, 1)) = False Then Rows(count + 1).Insert
      Next count
      End Sub
  • To post as a guest, your comment is unpublished.
    ermias · 1 years ago
    Here is my question and it is a very difficult one i guess.. is there a vba code that add a new row below a filtered column and copy just the first three cells into the added new row and continue doing so until the user stops hitting "enter" and unfilter the filtered cells?
  • To post as a guest, your comment is unpublished.
    Tiago Dias · 1 years ago
    I need huge help on this subject. I have 2 columns, on the 1st I have my data time 01/01/2016 05:00:00, days/months/year hour/minute/seconds and in the 2 2nd column the respective data associated to the time.

    My problem is that I want to add data time between rows since I have days gaps. 1st line is 01/01/2016 and the 2nd row has, for example, 10/01/2016, so I have 9 days. and that code doesn't work for me.

    Looking forward to getting some feedback, please! Thanks
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      You can try this VBA

      Sub InsertValueBetween()
      'Update 20130825
      Dim WorkRng As Range
      Dim Rng As Range
      Dim outArr As Variant
      Dim dic As Variant
      Set dic = CreateObject("Scripting.Dictionary")
      'On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      num1 = WorkRng.Range("A1").Value
      num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
      interval = num2 - num1
      ReDim outArr(1 To interval + 1, 1 To 2)
      For Each Rng In WorkRng
      dic(Rng.Value) = Rng.Offset(0, 1).Value
      Next
      For i = 0 To interval
      outArr(i + 1, 1) = i + num1
      If dic.Exists(i + num1) Then
      outArr(i + 1, 2) = dic(i + num1)
      Else
      outArr(i + 1, 2) = ""
      End If
      Next
      With WorkRng.Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2))
      .Value = outArr
      .Select
      End With
      End Sub


      Or if you have Kutools for Excel, you can try this function:
      • To post as a guest, your comment is unpublished.
        Tiago Dias · 1 years ago
        Hello again Sunny, i got some sucess on editing the code to this (i change the num1 line to A2 and With WorkRng.Range("A2:A100000").Resize(UBound(outArr, 1), UBound(outArr, 2)):


        Sub InsertValueBetween()
        'Update 20130825
        Dim WorkRng As Range
        Dim Rng As Range
        Dim outArr As Variant
        Dim dic As Variant
        Set dic = CreateObject("Scripting.Dictionary")
        'On Error Resume Next
        xTitleId = "KutoolsforExcel"
        Set WorkRng = Application.Selection
        Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
        num1 = WorkRng.Range("A2").Value
        num2 = WorkRng.Range("A" & WorkRng.Rows.Count).Value
        interval = num2 - num1
        ReDim outArr(1 To interval + 1, 1 To 2)
        For Each Rng In WorkRng
        dic(Rng.Value) = Rng.Offset(0, 1).Value
        Next
        For i = 0 To interval
        outArr(i + 1, 1) = i + num1
        If dic.Exists(i + num1) Then
        outArr(i + 1, 2) = dic(i + num1)
        Else
        outArr(i + 1, 2) = ""
        End If
        Next
        With WorkRng.Range("A2:A100000").Resize(UBound(outArr, 1), UBound(outArr, 2))
        .Value = outArr
        .Select
        End With
        End Sub



        I show you the graphs, it doesn't work 100% because it doesn't create the time from A1 to A2
      • To post as a guest, your comment is unpublished.
        Tiago Dias · 1 years ago
        Thanks a lot, I have tried both, the 1st one since I have like 500 rows of data, I do that for the all 500 rows and doesn't do anything, I think perhaps it has a limitation on the rows to use, and when I select just the first 5 rows, for example, it doesn't create the missing rows, replaces the rows for the missing data.

        Another problem that I have is that my time data has also the Day/Month/Year HH: MM: SS
        • To post as a guest, your comment is unpublished.
          Tiago Dias · 1 years ago
          From 2 to 3, it creates the missing data that I want ok, but the value of the 03/01/2016 is eliminated and there is some time data that is eliminated something that I don't want either
          • To post as a guest, your comment is unpublished.
            Sunny · 1 years ago
            Sorry the VBA code did not help you, I cannot find the method which can work for date and time format. If you find the solution finnally, could you let me know? Thank you.
  • To post as a guest, your comment is unpublished.
    Faissal sardar · 1 years ago
    How Can I insert more than one row ?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      if you want to insert blank rows below, try this

      Sub BlankLine()
      'Updateby20150203
      Dim Rng As Range
      Dim WorkRng As Range
      Dim xInsertNum As Long
      ' On Error Resume Next
      xTitleId = "Kutools for Excel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      If WorkRng Is Nothing Then Exit Sub
      xInsertNum = Application.InputBox("The number of blank rows you want to insert", xTitleId, Type:=1)
      If xInsertNum = False Then
      MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
      Exit Sub
      End If
      Set WorkRng = WorkRng.Columns(1)
      xLastRow = WorkRng.Rows.Count
      Application.ScreenUpdating = False
      For xRowIndex = xLastRow To 1 Step -1
      Set Rng = WorkRng.Range("A" & xRowIndex)
      If Rng.Value = "0" Then
      Rng.Offset(1, 0).Resize(xInsertNum).EntireRow.Insert Shift:=xlDown
      End If
      Next
      Application.ScreenUpdating = True
      End Sub

      The below one is to insert rows above.
      • To post as a guest, your comment is unpublished.
        Joubero Lambrechts · 4 months ago
        HI Sunny, this macro works perfectly for me; i just had to change the quantity of rows to 30 and change the 0 to text: "Closing Balance". But now i want to copy paste a selection of cells which is 30 rows high into the 30 blank lines which were just inserted by this macro. Can you suggest a new macro (or an amendment to this one) to copy and paste a range into each 30blanks lines. I have named the range to copy and paste 'template'.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      You can try this VBA

      Sub BlankLine()
      'Updateby20150203
      Dim Rng As Range
      Dim WorkRng As Range
      Dim xInsertNum As Long
      ' On Error Resume Next
      xTitleId = "Kutools for Excel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      If WorkRng Is Nothing Then Exit Sub
      xInsertNum = Application.InputBox("The number of blank rows you want to insert ", xTitleId, Type:=1)
      If xInsertNum = False Then
      MsgBox " The number of blank rows you want to insert ", vbInformation, xTitleId
      Exit Sub
      End If
      Set WorkRng = WorkRng.Columns(1)
      xLastRow = WorkRng.Rows.Count
      Application.ScreenUpdating = False
      For xRowIndex = xLastRow To 1 Step -1
      Set Rng = WorkRng.Range("A" & xRowIndex)
      If Rng.Value = "0" Then
      Rng.Resize(xInsertNum).EntireRow.Insert Shift:=xlDown
      End If
      Next
      Application.ScreenUpdating = True
      End Sub
  • To post as a guest, your comment is unpublished.
    pranav · 1 years ago
    That was amaaaazing!!. Thanks man.
  • To post as a guest, your comment is unpublished.
    Isidora · 1 years ago
    I love you. Thank you.
  • To post as a guest, your comment is unpublished.
    yatorres90 · 1 years ago
    I want to insert rows based on a count using a cell value in one spreadsheet and inserting rows in another spreadsheet.
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Thanks to your message. But can you discribe your question with more details? What rows do you want to insert? Blank? And Where you want to insert at in the sheet? If you can, give me some screenshhot. Thank you.
  • To post as a guest, your comment is unpublished.
    Rahul · 2 years ago
    Hi,
    I want to insert multiple rows based on the value
    Ex: I want to insert 1 blank row below the cell with value 2, 2 rows below the cell with value 3, 3 rows below the cell with value 4 and so on

    Can you please help me with this?
    • To post as a guest, your comment is unpublished.
      Ashley · 1 years ago
      DId you ever get an answer to this? I'm trying to do the same thing.

      Have a list of employees with # of weeks vacation they get. I want to insert a row for each week. It will be 1, 2 or 3 rows depending on how much time they've earned. the #s 1 2 3 are already in my spreadsheet.
  • To post as a guest, your comment is unpublished.
    Jafar · 4 years ago
    I want to paste specific content under below cell. How to do that? Instead of Blank row, I want to insert value in few columns.