Add a new row automatically

mgroeber

New Member
Joined
Dec 7, 2005
Messages
8
Hey guys,

I want to automatically add a new line whenever I enter information in a specific cell.

E.G.: I add text into cell C8, I would like to automatically add a new row below. same applies then for C9 etc.

Any advice?

Thanks,

Mike
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
since Excel has 65,000+ rows, I guess I don't understand what you are adding.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Hello Mike,
This will add a row just below the active cell's row whenever any change (other than to delete) is made in column C. (Note that this includes editing an existing value...) Is that what you're looking for, or is there more criteria to consider?

The code goes into the sheet code module.
(Right click the sheet tab, choose view code & paste this into the white area that is the sheet code module. Press AltQ to get back to your sheet.)
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Target(2, 1).EntireRow.Insert
End Sub
Hope it helps.
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
That's cool HalfAce,

How can that be done for only one cell and not the whole column?

RAM
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
Well, you could reference the cell itself by replacing the line:
Code:
If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
With...
Code:
If Intersect(Target, [C8]) Is Nothing Then Exit Sub
Or you could use a named range for your cell like so: (For instance if C8 were named "MyRng")
Code:
If Intersect(Target, Range("MyRng")) Is Nothing Then Exit Sub
Does that help?
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Thanks HalfAce,

I didn't try the range, but specified cell worked great.

RAM
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,899
Members
431,772
Latest member
dannyboi1

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