How to Auto-Insert Rows Based on Cell Values in Excel?

Let us assume we have a situation where we want to insert a row below or above a specified value. This process can?t be done by default in Excel, so we will be using the help of the VBA application to complete our process. Read this tutorial to learn how you can autoinsert rows in Excel based on cell values. If we try to complete this task manually, it can be a time-consuming process, as inserting a new row is a multi-step process.

AutoInsert Rows in Excel Based on Cell Values

Here we will first insert a VBA module and then run the code to complete the task. Let us see a simple process to understand how we can insert a row below based on a cell value in Excel.

Step 1

Let us consider an Excel sheet where the data is similar to the data shown in the below image.

Now right-click on the sheet name and select view code to open the vba application, then click on insert and select module, then type the programme into the text box as shown in the below image.

Example

<div class="code-mirror  language-javascript" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;">Sub <span class="token function">BlankLine</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
   'Updated by Nirmal
   Dim Rng As Range
   Dim WorkRng As Range
   On Error Resume Next
   xTitleId <span class="token operator">=</span> <span class="token string">"Enter the value"</span>
   Set WorkRng <span class="token operator">=</span> Application<span class="token punctuation">.</span>Selection
   Set WorkRng <span class="token operator">=</span> Application<span class="token punctuation">.</span><span class="token function">InputBox</span><span class="token punctuation">(</span><span class="token string">"Range"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> WorkRng<span class="token punctuation">.</span>Address<span class="token punctuation">,</span> Type<span class="token operator">:</span><span class="token operator">=</span><span class="token number">8</span><span class="token punctuation">)</span>
   Set WorkRng <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span><span class="token function">Columns</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>
   xLastRow <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span>Rows<span class="token punctuation">.</span>Count
   Application<span class="token punctuation">.</span>ScreenUpdating <span class="token operator">=</span> False
   For xRowIndex <span class="token operator">=</span> xLastRow To <span class="token number">1</span> Step <span class="token operator">-</span><span class="token number">1</span>
      Set Rng <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span><span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A"</span> <span class="token operator">&</span> xRowIndex<span class="token punctuation">)</span>
      If Rng<span class="token punctuation">.</span>Value <span class="token operator">=</span> <span class="token string">"10000"</span> Then
         Rng<span class="token punctuation">.</span><span class="token function">Offset</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">.</span>EntireRow<span class="token punctuation">.</span>Insert Shift<span class="token operator">:</span><span class="token operator">=</span>xlDown
      End If
   Next
   Application<span class="token punctuation">.</span>ScreenUpdating <span class="token operator">=</span> True
End Sub
</div>

In the code, 10000 is the cell value you want to insert.

Step 2

Save the sheet as a macro-enabled worksheet and press the F5 key to run the code. A pop-up window will appear; select the range to which the code should be applied, and our final output will look like the data in the image below.

As we can see, the new row is inserted below. To insert the row above the cell, use the following code ?

Example

<div class="code-mirror  language-javascript" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;">Sub <span class="token function">BlankLine</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
	'Update by Nirmal
	Dim Rng As Range
	Dim WorkRng As Range
	On Error Resume Next
	xTitleId                  <span class="token operator">=</span> <span class="token string">"Enter the range"</span>
	Set WorkRng        <span class="token operator">=</span> Application<span class="token punctuation">.</span>Selection
	Set WorkRng         <span class="token operator">=</span> Application<span class="token punctuation">.</span><span class="token function">InputBox</span><span class="token punctuation">(</span><span class="token string">"Range"</span><span class="token punctuation">,</span> xTitleId<span class="token punctuation">,</span> WorkRng<span class="token punctuation">.</span>Address<span class="token punctuation">,</span> Type<span class="token operator">:</span> <span class="token operator">=</span> <span class="token number">8</span><span class="token punctuation">)</span>
	Set WorkRng         <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span><span class="token function">Columns</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span>
	xLastRow               <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span>Rows<span class="token punctuation">.</span>Count
	Application<span class="token punctuation">.</span>ScreenUpdating <span class="token operator">=</span> False
	For xRowIndex <span class="token operator">=</span> xLastRow To <span class="token number">1</span> Step <span class="token operator">-</span> <span class="token number">1</span>
		Set Rng <span class="token operator">=</span> WorkRng<span class="token punctuation">.</span><span class="token function">Range</span><span class="token punctuation">(</span><span class="token string">"A"</span> <span class="token operator">&</span> xRowIndex<span class="token punctuation">)</span>
		If Rng<span class="token punctuation">.</span>Value <span class="token operator">=</span> <span class="token string">"0"</span> Then
			Rng<span class="token punctuation">.</span>EntireRow<span class="token punctuation">.</span>Insert Shift<span class="token operator">:</span> <span class="token operator">=</span> xlDown
		End If
	Next
	Application<span class="token punctuation">.</span>ScreenUpdating <span class="token operator">=</span> True
End Sub
</div>

Conclusion

In this tutorial, we used a simple example to demonstrate how we can insert a row based on cell values in Excel.