VBA to Insert Rows??

[CERipkenJr]

Hi there.

I have used the following code (copied from the Internet) with success - it inserts rows into my spreadsheet based on a number from another cell - perfect:

[TABLE="width: 448"]
<tbody>[TR]
[TD="colspan: 6"]Private Sub Worksheet_Change(ByVal Target As Range)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] If Target.AddressLocal = "$P$5" Then[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"] Dim i As Integer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"] For i = 1 To Target.Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"] Range("2:2").EntireRow.Insert[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Next i[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] End If[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Sub

How would I go about altering this code to add text to the row(s) created? (i.e. I need all inserted rows to say "Option Rent" in a particular cell of the added row(s).

Thanks a million.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

Perhaps something like this, change C:C to whatever column you want to add 'Option Rent' in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        Range("2:2").Resize(Target.Value).Insert
        Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
        
    End If
    
End Sub

 

[CERipkenJr]

Thanks so much. Follow up questions:

1. How can I eliminate the run-time error '1004': Application-defined or object-defined error when the value in cell P5 is deleted?

2. This won't run on a protected sheet - any way around that?

 

[Norie]

1 Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        If Not IsEmpty(Target) Then
            Range("2:2").Resize(Target.Value).Insert
            Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
        End If
        
    End If
    
End Sub

2 Perhaps something like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        If Not IsEmpty(Target) Then
            Me.Unprotect
            Range("2:2").Resize(Target.Value).Insert
            Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
            Me.Protect
        End If
        
    End If
    
End Sub

 

[CERipkenJr]

1 Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        If Not IsEmpty(Target) Then
            Range("2:2").Resize(Target.Value).Insert
            Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
        End If
        
    End If
    
End Sub

2 Perhaps something like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        If Not IsEmpty(Target) Then
            Me.Unprotect
            Range("2:2").Resize(Target.Value).Insert
            Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
            Me.Protect
        End If
        
    End If
    
End Sub

I love you. Thanks a million!

 

[CERipkenJr]

1 Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        If Not IsEmpty(Target) Then
            Range("2:2").Resize(Target.Value).Insert
            Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
        End If
        
    End If
    
End Sub

2 Perhaps something like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim I As Long

    If Target.AddressLocal = "$P$5" Then
        
        If Not IsEmpty(Target) Then
            Me.Unprotect
            Range("2:2").Resize(Target.Value).Insert
            Intersect(Range("C:C"), Range("2:2").Resize(Target.Value)).Value = "Option Rent"
            Me.Protect
        End If
        
    End If
    
End Sub

Ok, last question (I promise). (For now).

What about if, when a user deletes the data in P5, it deletes the previously inserted rows?

Also - how "advanced"-level are these questions that I'm asking? I'm VERY novice with VBA (obviously).