Auto Insert New Line

mmmarty

Board Regular
Joined
Oct 23, 2004
Messages
79
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
It is getting near the end of the week so bear with me.

I think you want someting like this in your loop

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

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,086
Members
412,310
Latest member
mark884
Top