Log in  \/ 
x
or
x
Register  \/ 
x

or

How to auto insert row based on cell value in Excel?

doc-insert-row-based-on-value-1
Suppose you have a range of data, and you want to auto insert blank rows above or below a certain value in Excel, for example, auto insert rows below zero value as below screenshot shown. In Excel, there is no direct way can solve this task, but I can introduce a Macro code for you to auto insert rows based on a certain value in Excel.
Insert row below based on cell value with VBA

Insert row above based on cell value with Kutools for Excel good idea3

To insert row based on cell value by running VBA, please do as below steps:

1. Press Alt + F11 keys simultaneously, and a Microsoft Visual Basic for Applications window pops out.

2. Click Insert > Module, then paste below VBA code to the popping Module window.

VBA: Insert row below based on cell value.

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. Click F5 key or the Run button, a dialog pops out, and select the column contains zero. See screenshot:
doc-insert-row-based-on-value-2

4. Click OK. Then blank rows will be inserted below zero value.
doc-insert-row-based-on-value-3

Tip:

1. If you want to insert rows based on other value, you can change 0 to any value you want in the VBA: If Rng.Value = "0" Then.

2. If you want to insert rows above zero or other value, you can use the below vba code.

VBA: Insert row above zero value:

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-row-based-on-value-4

If you are not familiar with VBA, you can try Kutools for Excel's Select Specific Cells utility, and then insert rows above.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 
Free Download
free full-featured in 30-day

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

1. Select the list you want to find out the specific cells from, and click Kutools > Select > Select Specific Cells. See screenshot:
doc insert row based on value 9

2. In the popping dialog, check Entire row option, and then go to select Equals from Specific type list, and then enter the value you want to find in the right textbox. See screenshot:
doc insert row based on value 6

3. Click Ok, and a dialog pops out to remind you the number of selected rows, just close it.

4. Place cursor at one selected row, and right click to select Insert from context menu. See screenshot:
doc insert row based on value 7

Now the rows are inserted above based on a specific value.
doc insert row based on value 8

Relative Articles:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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.
    mikeyrobbo · 2 months ago
    Many thanks for this, really a tremendous time saver. Would it be possible to add some code which allows me to insert some text in the new row. For example i'm inserting new rows based on target value 'x', then i want to add text value 'y' in the cell below target value 'x'. Is this possible?
  • To post as a guest, your comment is unpublished.
    Avi · 7 months ago
    Tell me the code where i want to add a particular number of rows based on a cell value. for example, if cell contains the digit 18, automatically 18 rows should be added where I want and the rest of the table/cell should shift downwards.
  • To post as a guest, your comment is unpublished.
    lowquark7@gmail.com · 1 years ago
    I need the macro to add rows based on a quantity column where if the qty is greater than 1, it inserts the number of rows -1. If the quantity is 5 it inserts 4 rows below it and fill down the data and change the qty called out from 5 to 1 each row. Skip all qty 1.
  • To post as a guest, your comment is unpublished.
    Ludovico · 1 years ago
    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


    I need this to work everytime i put something in the cell, and also with more variables. What i mean is that if i put 2 in the cell, i need it to insert 2 row and not just 1.
  • To post as a guest, your comment is unpublished.
    safa · 1 years 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.
      Sunny · 1 years ago
      Hi, Safa, maybe you can try Kutools' insert blank rows/columns utility.
  • To post as a guest, your comment is unpublished.
    Gina · 2 years 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.
      Sunny · 1 years ago
      Hi, Gina, I just find the code to insert blank row above the cell that is nonblank, maybe someone can adjust it to meet your need.

      Sub Insert_Rows()
      Dim LR As Long, r As Long

      Application.ScreenUpdating = False
      LR = Range("A" & Rows.Count).End(xlUp).Row
      For r = LR To 1 Step -1
      If Len(Range("A" & r).Value) > 0 Then
      Rows(r).Insert
      End If
      Next r
      Application.ScreenUpdating = True
      End Sub

      come from https://www.mrexcel.com/forum/excel-questions/548675-adding-blank-line-above-row-non-blank-cell.html
  • To post as a guest, your comment is unpublished.
    Louis · 2 years 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 · 2 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 · 2 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.
    ermi · 3 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.
      Sunny · 3 years ago
      Your question is somewhat difficult and complex, you can place the question in our forum, maybe someone can answer you. https://www.extendoffice.com/forum.html
  • To post as a guest, your comment is unpublished.
    tiagoagdias@gmail.com · 3 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 · 3 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.
        tiagoagdias@gmail.com · 3 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.
        tiagoagdias@gmail.com · 3 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.
          tiagoagdias@gmail.com · 3 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 · 3 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 · 3 years ago
    How Can I insert more than one row ?
    • To post as a guest, your comment is unpublished.
      Sunny · 3 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 · 1 years 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 · 3 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 · 3 years ago
    That was amaaaazing!!. Thanks man.
  • To post as a guest, your comment is unpublished.
    Isidora · 3 years ago
    I love you. Thank you.
  • To post as a guest, your comment is unpublished.
    yatorres90 · 3 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 · 3 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 · 4 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 · 3 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 · 5 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.