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

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

Select Cells/Rows/Columns with one or two criteria in Excel

The select Specific Cells of Kutools for Excel can quicky select all cells or rows or columns in a range based on one criterion or two criterion. Click for 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

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

Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group
as auto text, and liberate your brain! Click here to know Auto Text Click here to get free trial

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 60-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

Kutools for Excel: 300 + functions you must have in Excel, 60-day free trial from here

Relative Articles:

Insert blank rows in Excel

Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 30-day trial of Office Tab!

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.

Read More... Free Download... Purchase...

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!

Read More... Free Download... Purchase...

28 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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.
Ping Gordo · 14 days 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.
- Reply
- Share
To post as a guest, your comment is unpublished.
Ludovico · 3 months 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.
- Reply
- Share
To post as a guest, your comment is unpublished.
safa · 6 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.
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Sunny · 6 months ago
  
  Hi, Safa, maybe you can try Kutools' insert blank rows/columns utility.
  
  photo.png
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Gina · 1 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.
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Sunny · 7 months 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
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Louis · 1 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!!
- Reply
- Share
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!
- Reply
- Share
- 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
  
  Reply
  
  Share
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?
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Sunny · 1 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
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Tiago Dias · 2 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
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Sunny · 2 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:
  
  1.png
  
  2.png
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Tiago Dias · 2 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
  
  try.PNG
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Tiago Dias · 2 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
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Tiago Dias · 2 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
  
  1.PNG
  
  2.PNG
  
  3.PNG
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Sunny · 2 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.
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Faissal sardar · 2 years ago
How Can I insert more than one row ?
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Sunny · 2 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.
  
  Reply
  
  Share
  
  To post as a guest, your comment is unpublished.
  
  Joubero Lambrechts · 7 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'.
  
  Reply
  
  Share
- To post as a guest, your comment is unpublished.
  Sunny · 2 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
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
pranav · 2 years ago
That was amaaaazing!!. Thanks man.
- Reply
- Share
To post as a guest, your comment is unpublished.
Isidora · 2 years ago
I love you. Thank you.
- Reply
- Share
To post as a guest, your comment is unpublished.
yatorres90 · 2 years ago
I want to insert rows based on a count using a cell value in one spreadsheet and inserting rows in another spreadsheet.
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Sunny · 2 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.
  
  Reply
  
  Share
To post as a guest, your comment is unpublished.
Rahul · 3 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?
- Reply
- Share
- To post as a guest, your comment is unpublished.
  Ashley · 2 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.
  
  Reply
  
  Share
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.
- Reply
- Share

Login to your account

Password Reset

Reminder