Excel Marco For Inserting Row Is Copying To Many Column Crashing Excel

[bluemanuk1]

I have this Excel Macro which inserts a blank row above any cell in Column E that has the text "Shaped Bottom Finish" in it.

Sub BlankLine()

    Dim Col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long

        Col = "E"
        StartRow = 1
        BlankRows = 1

            LastRow = Cells(Rows.Count, Col).End(xlUp).Row

            Application.ScreenUpdating = False

            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1
If .Cells(R, Col) = "Shaped Bottom Finish" Then
.Cells(R, Col).EntireRow.Insert Shift:=xlDown
End If
Next R
End With
Application.ScreenUpdating = True

End Sub

Although it does perform this action it also copies this text in every column in the last row available on the sheet creating a huge file. It also crashes Excel.

Can anyone see anything in the code that isn't quite right?

I would appreciate any help.

[bluemanuk1]

I thought I should add , the Debug hi-lighted text is :

.Cells(R, Col).EntireRow.Insert Shift:=xlDown

Not sure if that helps.

[arlu1201]

Try this code

Option Explicit

Sub insert_row()
Dim i As Long, lrow As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")
    lrow = .Range("E" & .Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
        If .Range("E" & i).Value = "Shaped Bottom Finish" Then
            .Rows(i & ":" & i).Insert
            i = i - 1
        End If
    Next i
End With

Application.ScreenUpdating = True

End Sub

[bluemanuk1]

That works perfectly. Thank you for your prompt help!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.