Add a new row automatically

[mgroeber]

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

 

[nbrcrunch]

since Excel has 65,000+ rows, I guess I don't understand what you are adding.

 

[HalfAce]

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.)

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]

That's cool HalfAce,

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

RAM

 

[HalfAce]

Well, you could reference the cell itself by replacing the line:

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub

With...

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")

If Intersect(Target, Range("MyRng")) Is Nothing Then Exit Sub

Does that help?

 

[RAM]

Thanks HalfAce,

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

RAM