Insert Row Condition

Hello,

I need some help with searching each row for the word 'Contact' in column B
and when found I want to insert a blank new row just above the row with the
word 'Contact'

Thanks you in advance for any help
James

Hi

try the code

Please Login or Register  to view this content.

hope this helps
jindon

Try this macro James

Sub test1()
Dim Rng As Range
Dim findstring As String
findstring = "Contact"
Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
While Not Rng Is Nothing
Rng.EntireRow.Insert
Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
.Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
Wend
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"James" <[email protected]> wrote in message news:[email protected]...
> Hello,
>
> I need some help with searching each row for the word 'Contact' in column B and when found I want to insert a blank new row just
> above the row with the word 'Contact'
>
> Thanks you in advance for any help
> James
>

Thanks a lot Ron it did the trick.
Could I askof you to help with removing all rows that that are empty in
columns A thr m. I need to do this first before adding the rows between each
file.
Thanks for your help
James

"Ron de Bruin" <[email protected]> wrote in message
news:%[email protected]...
> Try this macro James
>
> Sub test1()
> Dim Rng As Range
> Dim findstring As String
> findstring = "Contact"
> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"),
> LookAt:=xlWhole)
> While Not Rng Is Nothing
> Rng.EntireRow.Insert
> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
> .Find(What:=findstring, After:=Range("B65536"),
> LookAt:=xlWhole)
> Wend
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "James" <[email protected]> wrote in message
> news:[email protected]...
>> Hello,
>>
>> I need some help with searching each row for the word 'Contact' in column
>> B and when found I want to insert a blank new row just above the row with
>> the word 'Contact'
>>
>> Thanks you in advance for any help
>> James
>>
>
>

Hi James

This macro will loop through all rows in the usedrange of the activesheet and delete
the row if A:M is empty

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "M"))) = 0 _
Then .Rows(Lrow).Delete
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl



"James" <[email protected]> wrote in message news:[email protected]...
> Thanks a lot Ron it did the trick.
> Could I askof you to help with removing all rows that that are empty in columns A thr m. I need to do this first before adding the
> rows between each file.
> Thanks for your help
> James
>
> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>> Try this macro James
>>
>> Sub test1()
>> Dim Rng As Range
>> Dim findstring As String
>> findstring = "Contact"
>> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>> While Not Rng Is Nothing
>> Rng.EntireRow.Insert
>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>> .Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>> Wend
>> End Sub
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "James" <[email protected]> wrote in message news:[email protected]...
>>> Hello,
>>>
>>> I need some help with searching each row for the word 'Contact' in column B and when found I want to insert a blank new row just
>>> above the row with the word 'Contact'
>>>
>>> Thanks you in advance for any help
>>> James
>>>
>>
>>
>
>

Thanks again for the help
James
"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Hi James
>
> This macro will loop through all rows in the usedrange of the activesheet
> and delete
> the row if A:M is empty
>
> Sub Example1()
> Dim Firstrow As Long
> Dim Lastrow As Long
> Dim Lrow As Long
> Dim CalcMode As Long
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
> With ActiveSheet
> .DisplayPageBreaks = False
> For Lrow = Lastrow To Firstrow Step -1
> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow,
> "M"))) = 0 _
> Then .Rows(Lrow).Delete
> Next
> End With
> With Application
> .ScreenUpdating = True
> .Calculation = CalcMode
> End With
> End Sub
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "James" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks a lot Ron it did the trick.
>> Could I askof you to help with removing all rows that that are empty in
>> columns A thr m. I need to do this first before adding the rows between
>> each file.
>> Thanks for your help
>> James
>>
>> "Ron de Bruin" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Try this macro James
>>>
>>> Sub test1()
>>> Dim Rng As Range
>>> Dim findstring As String
>>> findstring = "Contact"
>>> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"),
>>> LookAt:=xlWhole)
>>> While Not Rng Is Nothing
>>> Rng.EntireRow.Insert
>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>> .Find(What:=findstring, After:=Range("B65536"),
>>> LookAt:=xlWhole)
>>> Wend
>>> End Sub
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "James" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Hello,
>>>>
>>>> I need some help with searching each row for the word 'Contact' in
>>>> column B and when found I want to insert a blank new row just above the
>>>> row with the word 'Contact'
>>>>
>>>> Thanks you in advance for any help
>>>> James
>>>>
>>>
>>>
>>
>>
>
>

You are welcome



--
Regards Ron de Bruin
http://www.rondebruin.nl



"James" <[email protected]> wrote in message news:[email protected]...
> Thanks again for the help
> James
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>> Hi James
>>
>> This macro will loop through all rows in the usedrange of the activesheet and delete
>> the row if A:M is empty
>>
>> Sub Example1()
>> Dim Firstrow As Long
>> Dim Lastrow As Long
>> Dim Lrow As Long
>> Dim CalcMode As Long
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>> With ActiveSheet
>> .DisplayPageBreaks = False
>> For Lrow = Lastrow To Firstrow Step -1
>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "M"))) = 0 _
>> Then .Rows(Lrow).Delete
>> Next
>> End With
>> With Application
>> .ScreenUpdating = True
>> .Calculation = CalcMode
>> End With
>> End Sub
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "James" <[email protected]> wrote in message news:[email protected]...
>>> Thanks a lot Ron it did the trick.
>>> Could I askof you to help with removing all rows that that are empty in columns A thr m. I need to do this first before adding
>>> the rows between each file.
>>> Thanks for your help
>>> James
>>>
>>> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>>>> Try this macro James
>>>>
>>>> Sub test1()
>>>> Dim Rng As Range
>>>> Dim findstring As String
>>>> findstring = "Contact"
>>>> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>> While Not Rng Is Nothing
>>>> Rng.EntireRow.Insert
>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>> .Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>> Wend
>>>> End Sub
>>>>
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>>
>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>> Hello,
>>>>>
>>>>> I need some help with searching each row for the word 'Contact' in column B and when found I want to insert a blank new row
>>>>> just above the row with the word 'Contact'
>>>>>
>>>>> Thanks you in advance for any help
>>>>> James
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi Ron if I could trouble you for just one more item

Each one of my Contact files vary in the number of rows but are now
separated by an empty row thanks to you. Would it be possible to move the
each extra row of info of each file and append it to the first row starting
with the column N, my first row of each file always starts with the Word in
column B 'Contact'

This should do it and I'm sure I won't need ask you for anything else

Thanks again for the help to this point

James


"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> You are welcome
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "James" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks again for the help
>> James
>> "Ron de Bruin" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi James
>>>
>>> This macro will loop through all rows in the usedrange of the
>>> activesheet and delete
>>> the row if A:M is empty
>>>
>>> Sub Example1()
>>> Dim Firstrow As Long
>>> Dim Lastrow As Long
>>> Dim Lrow As Long
>>> Dim CalcMode As Long
>>>
>>> With Application
>>> CalcMode = .Calculation
>>> .Calculation = xlCalculationManual
>>> .ScreenUpdating = False
>>> End With
>>>
>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>> With ActiveSheet
>>> .DisplayPageBreaks = False
>>> For Lrow = Lastrow To Firstrow Step -1
>>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow,
>>> "M"))) = 0 _
>>> Then .Rows(Lrow).Delete
>>> Next
>>> End With
>>> With Application
>>> .ScreenUpdating = True
>>> .Calculation = CalcMode
>>> End With
>>> End Sub
>>>
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "James" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Thanks a lot Ron it did the trick.
>>>> Could I askof you to help with removing all rows that that are empty in
>>>> columns A thr m. I need to do this first before adding the rows between
>>>> each file.
>>>> Thanks for your help
>>>> James
>>>>
>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>> news:%[email protected]...
>>>>> Try this macro James
>>>>>
>>>>> Sub test1()
>>>>> Dim Rng As Range
>>>>> Dim findstring As String
>>>>> findstring = "Contact"
>>>>> Set Rng = Range("B:B").Find(What:=findstring,
>>>>> After:=Range("B65536"), LookAt:=xlWhole)
>>>>> While Not Rng Is Nothing
>>>>> Rng.EntireRow.Insert
>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>> .Find(What:=findstring, After:=Range("B65536"),
>>>>> LookAt:=xlWhole)
>>>>> Wend
>>>>> End Sub
>>>>>
>>>>>
>>>>> --
>>>>> Regards Ron de Bruin
>>>>> http://www.rondebruin.nl
>>>>>
>>>>>
>>>>>
>>>>> "James" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> Hello,
>>>>>>
>>>>>> I need some help with searching each row for the word 'Contact' in
>>>>>> column B and when found I want to insert a blank new row just above
>>>>>> the row with the word 'Contact'
>>>>>>
>>>>>> Thanks you in advance for any help
>>>>>> James
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Actually Ron scatch the append needs if the following can be done I only
wanted to do the append thing to make sorting easy. What I'm trying to do is
now that I have each file separated by the blank row is sort the file by the
city name which is in column I of the first row of each file which is the
row that contains the word 'Conatct' and keep all rows that belong to that
file together when the sort is run

Sorry for the confussion and I really appriciate the help
Jasmes


"James" <[email protected]> wrote in message
news:[email protected]...
> Hi Ron if I could trouble you for just one more item
>
> Each one of my Contact files vary in the number of rows but are now
> separated by an empty row thanks to you. Would it be possible to move the
> each extra row of info of each file and append it to the first row
> starting with the column N, my first row of each file always starts with
> the Word in column B 'Contact'
>
> This should do it and I'm sure I won't need ask you for anything else
>
> Thanks again for the help to this point
>
> James
>
>
> "Ron de Bruin" <[email protected]> wrote in message
> news:[email protected]...
>> You are welcome
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "James" <[email protected]> wrote in message
>> news:[email protected]...
>>> Thanks again for the help
>>> James
>>> "Ron de Bruin" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Hi James
>>>>
>>>> This macro will loop through all rows in the usedrange of the
>>>> activesheet and delete
>>>> the row if A:M is empty
>>>>
>>>> Sub Example1()
>>>> Dim Firstrow As Long
>>>> Dim Lastrow As Long
>>>> Dim Lrow As Long
>>>> Dim CalcMode As Long
>>>>
>>>> With Application
>>>> CalcMode = .Calculation
>>>> .Calculation = xlCalculationManual
>>>> .ScreenUpdating = False
>>>> End With
>>>>
>>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>>> With ActiveSheet
>>>> .DisplayPageBreaks = False
>>>> For Lrow = Lastrow To Firstrow Step -1
>>>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow,
>>>> "M"))) = 0 _
>>>> Then .Rows(Lrow).Delete
>>>> Next
>>>> End With
>>>> With Application
>>>> .ScreenUpdating = True
>>>> .Calculation = CalcMode
>>>> End With
>>>> End Sub
>>>>
>>>>
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>>
>>>> "James" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Thanks a lot Ron it did the trick.
>>>>> Could I askof you to help with removing all rows that that are empty
>>>>> in columns A thr m. I need to do this first before adding the rows
>>>>> between each file.
>>>>> Thanks for your help
>>>>> James
>>>>>
>>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>>> news:%[email protected]...
>>>>>> Try this macro James
>>>>>>
>>>>>> Sub test1()
>>>>>> Dim Rng As Range
>>>>>> Dim findstring As String
>>>>>> findstring = "Contact"
>>>>>> Set Rng = Range("B:B").Find(What:=findstring,
>>>>>> After:=Range("B65536"), LookAt:=xlWhole)
>>>>>> While Not Rng Is Nothing
>>>>>> Rng.EntireRow.Insert
>>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>>> .Find(What:=findstring, After:=Range("B65536"),
>>>>>> LookAt:=xlWhole)
>>>>>> Wend
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Regards Ron de Bruin
>>>>>> http://www.rondebruin.nl
>>>>>>
>>>>>>
>>>>>>
>>>>>> "James" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> Hello,
>>>>>>>
>>>>>>> I need some help with searching each row for the word 'Contact' in
>>>>>>> column B and when found I want to insert a blank new row just above
>>>>>>> the row with the word 'Contact'
>>>>>>>
>>>>>>> Thanks you in advance for any help
>>>>>>> James
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi James

I look at it tomorrow ( I must go now)




--
Regards Ron de Bruin
http://www.rondebruin.nl



"James" <[email protected]> wrote in message news:[email protected]...
> Actually Ron scatch the append needs if the following can be done I only wanted to do the append thing to make sorting easy. What
> I'm trying to do is now that I have each file separated by the blank row is sort the file by the city name which is in column I of
> the first row of each file which is the row that contains the word 'Conatct' and keep all rows that belong to that file together
> when the sort is run
>
> Sorry for the confussion and I really appriciate the help
> Jasmes
>
>
> "James" <[email protected]> wrote in message news:[email protected]...
>> Hi Ron if I could trouble you for just one more item
>>
>> Each one of my Contact files vary in the number of rows but are now separated by an empty row thanks to you. Would it be possible
>> to move the each extra row of info of each file and append it to the first row starting with the column N, my first row of each
>> file always starts with the Word in column B 'Contact'
>>
>> This should do it and I'm sure I won't need ask you for anything else
>>
>> Thanks again for the help to this point
>>
>> James
>>
>>
>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>> You are welcome
>>>
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>> Thanks again for the help
>>>> James
>>>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>>>> Hi James
>>>>>
>>>>> This macro will loop through all rows in the usedrange of the activesheet and delete
>>>>> the row if A:M is empty
>>>>>
>>>>> Sub Example1()
>>>>> Dim Firstrow As Long
>>>>> Dim Lastrow As Long
>>>>> Dim Lrow As Long
>>>>> Dim CalcMode As Long
>>>>>
>>>>> With Application
>>>>> CalcMode = .Calculation
>>>>> .Calculation = xlCalculationManual
>>>>> .ScreenUpdating = False
>>>>> End With
>>>>>
>>>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>>>> With ActiveSheet
>>>>> .DisplayPageBreaks = False
>>>>> For Lrow = Lastrow To Firstrow Step -1
>>>>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "M"))) = 0 _
>>>>> Then .Rows(Lrow).Delete
>>>>> Next
>>>>> End With
>>>>> With Application
>>>>> .ScreenUpdating = True
>>>>> .Calculation = CalcMode
>>>>> End With
>>>>> End Sub
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards Ron de Bruin
>>>>> http://www.rondebruin.nl
>>>>>
>>>>>
>>>>>
>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>> Thanks a lot Ron it did the trick.
>>>>>> Could I askof you to help with removing all rows that that are empty in columns A thr m. I need to do this first before
>>>>>> adding the rows between each file.
>>>>>> Thanks for your help
>>>>>> James
>>>>>>
>>>>>> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>>>>>>> Try this macro James
>>>>>>>
>>>>>>> Sub test1()
>>>>>>> Dim Rng As Range
>>>>>>> Dim findstring As String
>>>>>>> findstring = "Contact"
>>>>>>> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>> While Not Rng Is Nothing
>>>>>>> Rng.EntireRow.Insert
>>>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>>>> .Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>> Wend
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Regards Ron de Bruin
>>>>>>> http://www.rondebruin.nl
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> Hello,
>>>>>>>>
>>>>>>>> I need some help with searching each row for the word 'Contact' in column B and when found I want to insert a blank new row
>>>>>>>> just above the row with the word 'Contact'
>>>>>>>>
>>>>>>>> Thanks you in advance for any help
>>>>>>>> James
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

That's great, anytime really
Appriciate the help
James

"Ron de Bruin" <[email protected]> wrote in message
news:%[email protected]...
> Hi James
>
> I look at it tomorrow ( I must go now)
>
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "James" <[email protected]> wrote in message
> news:[email protected]...
>> Actually Ron scatch the append needs if the following can be done I only
>> wanted to do the append thing to make sorting easy. What I'm trying to do
>> is now that I have each file separated by the blank row is sort the file
>> by the city name which is in column I of the first row of each file which
>> is the row that contains the word 'Conatct' and keep all rows that belong
>> to that file together when the sort is run
>>
>> Sorry for the confussion and I really appriciate the help
>> Jasmes
>>
>>
>> "James" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi Ron if I could trouble you for just one more item
>>>
>>> Each one of my Contact files vary in the number of rows but are now
>>> separated by an empty row thanks to you. Would it be possible to move
>>> the each extra row of info of each file and append it to the first row
>>> starting with the column N, my first row of each file always starts with
>>> the Word in column B 'Contact'
>>>
>>> This should do it and I'm sure I won't need ask you for anything else
>>>
>>> Thanks again for the help to this point
>>>
>>> James
>>>
>>>
>>> "Ron de Bruin" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> You are welcome
>>>>
>>>>
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>>
>>>> "James" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Thanks again for the help
>>>>> James
>>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> Hi James
>>>>>>
>>>>>> This macro will loop through all rows in the usedrange of the
>>>>>> activesheet and delete
>>>>>> the row if A:M is empty
>>>>>>
>>>>>> Sub Example1()
>>>>>> Dim Firstrow As Long
>>>>>> Dim Lastrow As Long
>>>>>> Dim Lrow As Long
>>>>>> Dim CalcMode As Long
>>>>>>
>>>>>> With Application
>>>>>> CalcMode = .Calculation
>>>>>> .Calculation = xlCalculationManual
>>>>>> .ScreenUpdating = False
>>>>>> End With
>>>>>>
>>>>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>>>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>>>>> With ActiveSheet
>>>>>> .DisplayPageBreaks = False
>>>>>> For Lrow = Lastrow To Firstrow Step -1
>>>>>> If Application.CountA(.Range(.Cells(Lrow, "A"),
>>>>>> .Cells(Lrow, "M"))) = 0 _
>>>>>> Then .Rows(Lrow).Delete
>>>>>> Next
>>>>>> End With
>>>>>> With Application
>>>>>> .ScreenUpdating = True
>>>>>> .Calculation = CalcMode
>>>>>> End With
>>>>>> End Sub
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Regards Ron de Bruin
>>>>>> http://www.rondebruin.nl
>>>>>>
>>>>>>
>>>>>>
>>>>>> "James" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> Thanks a lot Ron it did the trick.
>>>>>>> Could I askof you to help with removing all rows that that are empty
>>>>>>> in columns A thr m. I need to do this first before adding the rows
>>>>>>> between each file.
>>>>>>> Thanks for your help
>>>>>>> James
>>>>>>>
>>>>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>>>>> news:%[email protected]...
>>>>>>>> Try this macro James
>>>>>>>>
>>>>>>>> Sub test1()
>>>>>>>> Dim Rng As Range
>>>>>>>> Dim findstring As String
>>>>>>>> findstring = "Contact"
>>>>>>>> Set Rng = Range("B:B").Find(What:=findstring,
>>>>>>>> After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>>> While Not Rng Is Nothing
>>>>>>>> Rng.EntireRow.Insert
>>>>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>>>>> .Find(What:=findstring, After:=Range("B65536"),
>>>>>>>> LookAt:=xlWhole)
>>>>>>>> Wend
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards Ron de Bruin
>>>>>>>> http://www.rondebruin.nl
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> "James" <[email protected]> wrote in message
>>>>>>>> news:[email protected]...
>>>>>>>>> Hello,
>>>>>>>>>
>>>>>>>>> I need some help with searching each row for the word 'Contact' in
>>>>>>>>> column B and when found I want to insert a blank new row just
>>>>>>>>> above the row with the word 'Contact'
>>>>>>>>>
>>>>>>>>> Thanks you in advance for any help
>>>>>>>>> James
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi James

If I understand you correct?

You can use Autofilter on column B for 'Contact'
Then sort on the I column


--
Regards Ron de Bruin
http://www.rondebruin.nl



"James" <[email protected]> wrote in message news:[email protected]...
> That's great, anytime really
> Appriciate the help
> James
>
> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>> Hi James
>>
>> I look at it tomorrow ( I must go now)
>>
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "James" <[email protected]> wrote in message news:[email protected]...
>>> Actually Ron scatch the append needs if the following can be done I only wanted to do the append thing to make sorting easy.
>>> What I'm trying to do is now that I have each file separated by the blank row is sort the file by the city name which is in
>>> column I of the first row of each file which is the row that contains the word 'Conatct' and keep all rows that belong to that
>>> file together when the sort is run
>>>
>>> Sorry for the confussion and I really appriciate the help
>>> Jasmes
>>>
>>>
>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>> Hi Ron if I could trouble you for just one more item
>>>>
>>>> Each one of my Contact files vary in the number of rows but are now separated by an empty row thanks to you. Would it be
>>>> possible to move the each extra row of info of each file and append it to the first row starting with the column N, my first
>>>> row of each file always starts with the Word in column B 'Contact'
>>>>
>>>> This should do it and I'm sure I won't need ask you for anything else
>>>>
>>>> Thanks again for the help to this point
>>>>
>>>> James
>>>>
>>>>
>>>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>>>> You are welcome
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards Ron de Bruin
>>>>> http://www.rondebruin.nl
>>>>>
>>>>>
>>>>>
>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>> Thanks again for the help
>>>>>> James
>>>>>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>>>>>> Hi James
>>>>>>>
>>>>>>> This macro will loop through all rows in the usedrange of the activesheet and delete
>>>>>>> the row if A:M is empty
>>>>>>>
>>>>>>> Sub Example1()
>>>>>>> Dim Firstrow As Long
>>>>>>> Dim Lastrow As Long
>>>>>>> Dim Lrow As Long
>>>>>>> Dim CalcMode As Long
>>>>>>>
>>>>>>> With Application
>>>>>>> CalcMode = .Calculation
>>>>>>> .Calculation = xlCalculationManual
>>>>>>> .ScreenUpdating = False
>>>>>>> End With
>>>>>>>
>>>>>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>>>>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>>>>>> With ActiveSheet
>>>>>>> .DisplayPageBreaks = False
>>>>>>> For Lrow = Lastrow To Firstrow Step -1
>>>>>>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "M"))) = 0 _
>>>>>>> Then .Rows(Lrow).Delete
>>>>>>> Next
>>>>>>> End With
>>>>>>> With Application
>>>>>>> .ScreenUpdating = True
>>>>>>> .Calculation = CalcMode
>>>>>>> End With
>>>>>>> End Sub
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Regards Ron de Bruin
>>>>>>> http://www.rondebruin.nl
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> Thanks a lot Ron it did the trick.
>>>>>>>> Could I askof you to help with removing all rows that that are empty in columns A thr m. I need to do this first before
>>>>>>>> adding the rows between each file.
>>>>>>>> Thanks for your help
>>>>>>>> James
>>>>>>>>
>>>>>>>> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>> Try this macro James
>>>>>>>>>
>>>>>>>>> Sub test1()
>>>>>>>>> Dim Rng As Range
>>>>>>>>> Dim findstring As String
>>>>>>>>> findstring = "Contact"
>>>>>>>>> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>>>> While Not Rng Is Nothing
>>>>>>>>> Rng.EntireRow.Insert
>>>>>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>>>>>> .Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>>>> Wend
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards Ron de Bruin
>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>> Hello,
>>>>>>>>>>
>>>>>>>>>> I need some help with searching each row for the word 'Contact' in column B and when found I want to insert a blank new
>>>>>>>>>> row just above the row with the word 'Contact'
>>>>>>>>>>
>>>>>>>>>> Thanks you in advance for any help
>>>>>>>>>> James
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi Ron,

I sorry I did not explain properly as to what I need amd thanks so much in
working with me here or maybe you did understand and I'm not finding what I
need with the standard filter & sort tools in Excel. I will try to be more
clear below about my need.

My worksheet is a customer sales forecast and each customer record varies is
the number of rows it uses, haveing a blank row between each customer record
and over 2000 records on the sheet. Each customer record will occupie say
(A1:J1), (A2:J2), (A3:J3) and sometime will use more rows and each cell
contains information. I want to sort the 2000 + records using cell (AI)
which is the cell that contains the city name Say 'Los Angeles'. I only
mentioned the Word 'Contact' because it is a constant and always occupies
the first cell in each record in column A

I know sending workbooks is something one does not want to do but if you
don't mind I could send a short sample safely scanned book

Thanks agian for trying to work with me

James

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
>
> Hi James
>
> If I understand you correct?
>
> You can use Autofilter on column B for 'Contact'
> Then sort on the I column
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "James" <[email protected]> wrote in message
> news:[email protected]...
>> That's great, anytime really
>> Appriciate the help
>> James
>>
>> "Ron de Bruin" <[email protected]> wrote in message
>> news:%[email protected]...
>>> Hi James
>>>
>>> I look at it tomorrow ( I must go now)
>>>
>>>
>>>
>>>
>>> --
>>> Regards Ron de Bruin
>>> http://www.rondebruin.nl
>>>
>>>
>>>
>>> "James" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Actually Ron scatch the append needs if the following can be done I
>>>> only wanted to do the append thing to make sorting easy. What I'm
>>>> trying to do is now that I have each file separated by the blank row is
>>>> sort the file by the city name which is in column I of the first row of
>>>> each file which is the row that contains the word 'Conatct' and keep
>>>> all rows that belong to that file together when the sort is run
>>>>
>>>> Sorry for the confussion and I really appriciate the help
>>>> Jasmes
>>>>
>>>>
>>>> "James" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> Hi Ron if I could trouble you for just one more item
>>>>>
>>>>> Each one of my Contact files vary in the number of rows but are now
>>>>> separated by an empty row thanks to you. Would it be possible to move
>>>>> the each extra row of info of each file and append it to the first row
>>>>> starting with the column N, my first row of each file always starts
>>>>> with the Word in column B 'Contact'
>>>>>
>>>>> This should do it and I'm sure I won't need ask you for anything else
>>>>>
>>>>> Thanks again for the help to this point
>>>>>
>>>>> James
>>>>>
>>>>>
>>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>> You are welcome
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Regards Ron de Bruin
>>>>>> http://www.rondebruin.nl
>>>>>>
>>>>>>
>>>>>>
>>>>>> "James" <[email protected]> wrote in message
>>>>>> news:[email protected]...
>>>>>>> Thanks again for the help
>>>>>>> James
>>>>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>>>>> news:[email protected]...
>>>>>>>> Hi James
>>>>>>>>
>>>>>>>> This macro will loop through all rows in the usedrange of the
>>>>>>>> activesheet and delete
>>>>>>>> the row if A:M is empty
>>>>>>>>
>>>>>>>> Sub Example1()
>>>>>>>> Dim Firstrow As Long
>>>>>>>> Dim Lastrow As Long
>>>>>>>> Dim Lrow As Long
>>>>>>>> Dim CalcMode As Long
>>>>>>>>
>>>>>>>> With Application
>>>>>>>> CalcMode = .Calculation
>>>>>>>> .Calculation = xlCalculationManual
>>>>>>>> .ScreenUpdating = False
>>>>>>>> End With
>>>>>>>>
>>>>>>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>>>>>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>>>>>>> With ActiveSheet
>>>>>>>> .DisplayPageBreaks = False
>>>>>>>> For Lrow = Lastrow To Firstrow Step -1
>>>>>>>> If Application.CountA(.Range(.Cells(Lrow, "A"),
>>>>>>>> .Cells(Lrow, "M"))) = 0 _
>>>>>>>> Then .Rows(Lrow).Delete
>>>>>>>> Next
>>>>>>>> End With
>>>>>>>> With Application
>>>>>>>> .ScreenUpdating = True
>>>>>>>> .Calculation = CalcMode
>>>>>>>> End With
>>>>>>>> End Sub
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Regards Ron de Bruin
>>>>>>>> http://www.rondebruin.nl
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> "James" <[email protected]> wrote in message
>>>>>>>> news:[email protected]...
>>>>>>>>> Thanks a lot Ron it did the trick.
>>>>>>>>> Could I askof you to help with removing all rows that that are
>>>>>>>>> empty in columns A thr m. I need to do this first before adding
>>>>>>>>> the rows between each file.
>>>>>>>>> Thanks for your help
>>>>>>>>> James
>>>>>>>>>
>>>>>>>>> "Ron de Bruin" <[email protected]> wrote in message
>>>>>>>>> news:%[email protected]...
>>>>>>>>>> Try this macro James
>>>>>>>>>>
>>>>>>>>>> Sub test1()
>>>>>>>>>> Dim Rng As Range
>>>>>>>>>> Dim findstring As String
>>>>>>>>>> findstring = "Contact"
>>>>>>>>>> Set Rng = Range("B:B").Find(What:=findstring,
>>>>>>>>>> After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>>>>> While Not Rng Is Nothing
>>>>>>>>>> Rng.EntireRow.Insert
>>>>>>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>>>>>>> .Find(What:=findstring, After:=Range("B65536"),
>>>>>>>>>> LookAt:=xlWhole)
>>>>>>>>>> Wend
>>>>>>>>>> End Sub
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "James" <[email protected]> wrote in message
>>>>>>>>>> news:[email protected]...
>>>>>>>>>>> Hello,
>>>>>>>>>>>
>>>>>>>>>>> I need some help with searching each row for the word 'Contact'
>>>>>>>>>>> in column B and when found I want to insert a blank new row just
>>>>>>>>>>> above the row with the word 'Contact'
>>>>>>>>>>>
>>>>>>>>>>> Thanks you in advance for any help
>>>>>>>>>>> James
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi James

Send me a example workbook and I will see what I can do.
Not so easy I think?

--
Regards Ron de Bruin
http://www.rondebruin.nl



"James" <[email protected]> wrote in message news:[email protected]...
> Hi Ron,
>
> I sorry I did not explain properly as to what I need amd thanks so much in working with me here or maybe you did understand and
> I'm not finding what I need with the standard filter & sort tools in Excel. I will try to be more clear below about my need.
>
> My worksheet is a customer sales forecast and each customer record varies is the number of rows it uses, haveing a blank row
> between each customer record and over 2000 records on the sheet. Each customer record will occupie say (A1:J1), (A2:J2), (A3:J3)
> and sometime will use more rows and each cell contains information. I want to sort the 2000 + records using cell (AI) which is the
> cell that contains the city name Say 'Los Angeles'. I only mentioned the Word 'Contact' because it is a constant and always
> occupies the first cell in each record in column A
>
> I know sending workbooks is something one does not want to do but if you don't mind I could send a short sample safely scanned
> book
>
> Thanks agian for trying to work with me
>
> James
>
> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>
>> Hi James
>>
>> If I understand you correct?
>>
>> You can use Autofilter on column B for 'Contact'
>> Then sort on the I column
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>>
>> "James" <[email protected]> wrote in message news:[email protected]...
>>> That's great, anytime really
>>> Appriciate the help
>>> James
>>>
>>> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>>>> Hi James
>>>>
>>>> I look at it tomorrow ( I must go now)
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Regards Ron de Bruin
>>>> http://www.rondebruin.nl
>>>>
>>>>
>>>>
>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>> Actually Ron scatch the append needs if the following can be done I only wanted to do the append thing to make sorting easy.
>>>>> What I'm trying to do is now that I have each file separated by the blank row is sort the file by the city name which is in
>>>>> column I of the first row of each file which is the row that contains the word 'Conatct' and keep all rows that belong to that
>>>>> file together when the sort is run
>>>>>
>>>>> Sorry for the confussion and I really appriciate the help
>>>>> Jasmes
>>>>>
>>>>>
>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>> Hi Ron if I could trouble you for just one more item
>>>>>>
>>>>>> Each one of my Contact files vary in the number of rows but are now separated by an empty row thanks to you. Would it be
>>>>>> possible to move the each extra row of info of each file and append it to the first row starting with the column N, my first
>>>>>> row of each file always starts with the Word in column B 'Contact'
>>>>>>
>>>>>> This should do it and I'm sure I won't need ask you for anything else
>>>>>>
>>>>>> Thanks again for the help to this point
>>>>>>
>>>>>> James
>>>>>>
>>>>>>
>>>>>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>>>>>> You are welcome
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Regards Ron de Bruin
>>>>>>> http://www.rondebruin.nl
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>>>> Thanks again for the help
>>>>>>>> James
>>>>>>>> "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>> Hi James
>>>>>>>>>
>>>>>>>>> This macro will loop through all rows in the usedrange of the activesheet and delete
>>>>>>>>> the row if A:M is empty
>>>>>>>>>
>>>>>>>>> Sub Example1()
>>>>>>>>> Dim Firstrow As Long
>>>>>>>>> Dim Lastrow As Long
>>>>>>>>> Dim Lrow As Long
>>>>>>>>> Dim CalcMode As Long
>>>>>>>>>
>>>>>>>>> With Application
>>>>>>>>> CalcMode = .Calculation
>>>>>>>>> .Calculation = xlCalculationManual
>>>>>>>>> .ScreenUpdating = False
>>>>>>>>> End With
>>>>>>>>>
>>>>>>>>> Firstrow = ActiveSheet.UsedRange.Cells(1).Row
>>>>>>>>> Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
>>>>>>>>> With ActiveSheet
>>>>>>>>> .DisplayPageBreaks = False
>>>>>>>>> For Lrow = Lastrow To Firstrow Step -1
>>>>>>>>> If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "M"))) = 0 _
>>>>>>>>> Then .Rows(Lrow).Delete
>>>>>>>>> Next
>>>>>>>>> End With
>>>>>>>>> With Application
>>>>>>>>> .ScreenUpdating = True
>>>>>>>>> .Calculation = CalcMode
>>>>>>>>> End With
>>>>>>>>> End Sub
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards Ron de Bruin
>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>> Thanks a lot Ron it did the trick.
>>>>>>>>>> Could I askof you to help with removing all rows that that are empty in columns A thr m. I need to do this first before
>>>>>>>>>> adding the rows between each file.
>>>>>>>>>> Thanks for your help
>>>>>>>>>> James
>>>>>>>>>>
>>>>>>>>>> "Ron de Bruin" <[email protected]> wrote in message news:%[email protected]...
>>>>>>>>>>> Try this macro James
>>>>>>>>>>>
>>>>>>>>>>> Sub test1()
>>>>>>>>>>> Dim Rng As Range
>>>>>>>>>>> Dim findstring As String
>>>>>>>>>>> findstring = "Contact"
>>>>>>>>>>> Set Rng = Range("B:B").Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>>>>>> While Not Rng Is Nothing
>>>>>>>>>>> Rng.EntireRow.Insert
>>>>>>>>>>> Set Rng = Range("B" & Rng.Row + 1 & ":B" & Rows.Count) _
>>>>>>>>>>> .Find(What:=findstring, After:=Range("B65536"), LookAt:=xlWhole)
>>>>>>>>>>> Wend
>>>>>>>>>>> End Sub
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> --
>>>>>>>>>>> Regards Ron de Bruin
>>>>>>>>>>> http://www.rondebruin.nl
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> "James" <[email protected]> wrote in message news:[email protected]...
>>>>>>>>>>>> Hello,
>>>>>>>>>>>>
>>>>>>>>>>>> I need some help with searching each row for the word 'Contact' in column B and when found I want to insert a blank new
>>>>>>>>>>>> row just above the row with the word 'Contact'
>>>>>>>>>>>>
>>>>>>>>>>>> Thanks you in advance for any help
>>>>>>>>>>>> James
>>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>