Macro to Insert Rows After a given Text in Col A

[Rashid Khan]

Hello All,
I am using Office XP and have this macro from the NG. I have a long list of
data in Column A. This macro inserts a blank row (after) looking up the
text in A1 viz "Membership "

How can I change it to insert a blank row (before) the word Membership in
all the rows.

Sub InsertRows()
Dim cRows As Long
Dim i As Integer

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Left(Cells(i, "A"), 11) = "Membership " Then
Cells(i + 1, "A").EntireRow.Insert
End If
Next i
End Sub

TIA
Rashid

 

[Bob Phillips]

Sub InsertRows()
Dim cRows As Long
Dim i As Integer

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Left(Cells(i, "A"), 11) = "Membership " Then
Cells(i , "A").EntireRow.Insert
End If
Next i
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Just lose the + 1 in the 'Cells(i + 1, "A").EntireRow.Insert' line

Sub InsertRows()
Dim cRows As Long
Dim i As Integer

For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Left(Cells(i, "A"), 11) = "Membership " Then
Cells(i, "A").EntireRow.Insert
End If
Next i
End Sub

 

[Rashid Khan]

Hello Bob and Ken,
Thank you both for your help. It works fine.
Rashid

 

[Ken Wright]

You're welcome