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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,453
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,453
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,948
Members
412,299
Latest member
agentless
Top