Insert Row below current row when...

russmeister

New Member
Joined
Aug 19, 2013
Messages
1
I want to insert a new row with the existing formulas that I have in the current row. I want this row to be below the current row and I want it to insert when data in column A is changed from empty to a selection from a dropdown list. Can anyone help with this?
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Jeffrey Lopez

Active Member
Joined
Jul 9, 2013
Messages
495
Welcome to the Mr. Excel Message Board!

So first this is going to need to be a Worksheet_Change event, so it will need to be put in the Worksheet code. (Right Click on the Sheet Name > View Code > Paste it there)

Worksheet_Code.gif


The code you might want to try would be something like this:

First we need to define what range to look for the changes in, in this example I am just using A:A. You can change this to more specific range if you like, something like: A1:A25 or whatever, but as of now it will look for any change in the column A.

Code:
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

Now we need to tell it what to look for and what to do with it. I am using a simple IF the value is great than 0 (which is basically everything).

Code:
If Target.Value > 0 Then

Next we need to tell it what do when everything is true:

Code:
Target.Offset(1, 0).EntireRow.Insert shift:=xlDown
Range(Target.Offset(0, 1), Target.Offset(0, 10)).Copy Destination:=Target.Offset(1, 1)
[CODE]

Basically, we are saying, Where it finds the change, 1 row down (Offset(1,0)) insert a row, then copy the range B2:K2 or (Target.Offset(0, 1), Target.Offset(0, 10)) and paste it in the row we just inserted.  If you need more than 10 columns just change the Target.Offset(0,10) to the total number of columns you want to copy...

And that is it... I hope that helps, and as always, [COLOR=#2f4f4f][B]please back up your excel workbook before running this macro![/B][/COLOR]

[CODE]
Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ErrorHandler:
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub


If Target.Value > 0 Then


Target.Offset(1, 0).EntireRow.Insert shift:=xlDown


' Change the Target.Offset(0,10) to however many columns there are in your tabel...
Range(Target.Offset(0, 1), Target.Offset(0, 10)).Copy Destination:=Target.Offset(1, 1)


End If
ErrorHandler:
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,773
Messages
5,598,010
Members
414,202
Latest member
tejprn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top