Macro to Insert Rows After a given Text in Col A
 

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

Macro to Insert Rows After a given Text in Col A
 

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)

"Rashid Khan" wrote in message
...
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

Macro to Insert Rows After a given Text in Col A
 

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Rashid Khan" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004

Thanks Macro to Insert Rows After a given Text in Col A
 

Hello Bob and Ken,
Thank you both for your help. It works fine.
Rashid
"Ken Wright" wrote in message
...
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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Rashid Khan" wrote in message
...
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




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004

Thanks Macro to Insert Rows After a given Text in Col A
 

You're welcome

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.726 / Virus Database: 481 - Release Date: 22/07/2004