VBA assist inserting rows with text before and after in same column that insert depend son

[elmar007]

Hi all

I am using the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 9 Or Target.Count <> 1 Then Exit Sub
If Target.Row = Cells(65536, 9).End(xlUp).Row Then _
Target.Offset(1, 0).EntireRow.Insert
End Sub

it allows me to insert a blank row after I input in Column I.

However if I have anything in column I after where I want to input it does not work

ie. If I input data in cell I2 (and there is no other data in column I down fron I2) it automatically generates a new row 3 (this is what I want it to do).

However if I already had data in cell I7 then I inputted into cell I2 it would not generate my new row 3.

Any advise please on modifying my code

RM

 

[JackDanIce]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column <> 9 Or Target.Count <> 1 Then Exit Sub
    Target.Offset(1).EntireRow.Insert
End Sub

 

[elmar007]

Nice one JDI

That is a much cleaner code

Ok now it gets more complicated

How do I expand code to include Col J & Col K ie. 10 and 11?

These rows are completed after Col I. if there are enough rows put in from col I then no need to add rows

cheers