Auto Insert New Line

[mmmarty]

Can someone tell me where I'm astray on this.
This small script peers down column B:B looking for instances of the word "~AutoLine".
If there is only 1 empty cell in B:B above the word "~AutoLine" then an entire row is self inserted forcing the "~AutoLine" line to move down by one row.

The good part is the newly inserted row copies the contents of the last line, but it also copies the trigger word in B:B "~AutoLine".

Is there a way to clear just this one NEW instance in the B:B cell containing the word "~AutoLine" ??


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Cntr As Integer

If Target.Cells.Count > 2 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
On Error Resume Next

With Range("B:B")
Set Rng = .Find(what:="~AutoLine", LookIn:=xlFormulas)

If Not Rng Is Nothing Then

Do
Cntr = Cntr + 1
If Not IsEmpty(Rng.Offset(-1)) Then

Rng.EntireRow.Copy

Rng.EntireRow.Insert Shift:=xlDown

Rng.EntireRow.PasteSpecial
End If

Set Rng = .FindNext(Rng)
If Rng Is Nothing Then Exit Do
Loop Until Cntr >= 30

End If
End With


End Sub

 

[BrianB]

It is getting near the end of the week so bear with me.

I think you want someting like this in your loop

ActiveSheet.Cells(rng.Row, 2).ClearContents