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

Insert Row Condition
 

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

Insert Row Condition
 

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

Insert Row Condition
 

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

Insert Row Condition
 

Thanks again for the help
James
"Ron de Bruin" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Insert Row Condition
 

You are welcome



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



"James" wrote in message ...
Thanks again for the help
James
"Ron de Bruin" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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

Insert Row Condition
 

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" wrote in message
...
You are welcome



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



"James" wrote in message
...
Thanks again for the help
James
"Ron de Bruin" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Insert Row Condition
 

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" wrote in message
...
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" wrote in message
...
You are welcome



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



"James" wrote in message
...
Thanks again for the help
James
"Ron de Bruin" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Insert Row Condition
 

Hi James

I look at it tomorrow ( I must go now)




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



"James" wrote in message ...
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" wrote in message ...
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" wrote in message ...
You are welcome



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



"James" wrote in message ...
Thanks again for the help
James
"Ron de Bruin" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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

Insert Row Condition
 

That's great, anytime really
Appriciate the help
James

"Ron de Bruin" wrote in message
...
Hi James

I look at it tomorrow ( I must go now)




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



"James" wrote in message
...
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" wrote in message
...
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" wrote in message
...
You are welcome



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



"James" wrote in message
...
Thanks again for the help
James
"Ron de Bruin" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Insert Row Condition
 

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" wrote in message ...
That's great, anytime really
Appriciate the help
James

"Ron de Bruin" wrote in message ...
Hi James

I look at it tomorrow ( I must go now)




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



"James" wrote in message ...
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" wrote in message ...
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" wrote in message ...
You are welcome



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



"James" wrote in message ...
Thanks again for the help
James
"Ron de Bruin" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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

Insert Row Condition
 

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

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" wrote in message
...
That's great, anytime really
Appriciate the help
James

"Ron de Bruin" wrote in message
...
Hi James

I look at it tomorrow ( I must go now)




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



"James" wrote in message
...
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" wrote in message
...
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" wrote in message
...
You are welcome



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



"James" wrote in message
...
Thanks again for the help
James
"Ron de Bruin" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

Insert Row Condition
 

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

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" wrote in message ...
That's great, anytime really
Appriciate the help
James

"Ron de Bruin" wrote in message ...
Hi James

I look at it tomorrow ( I must go now)




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



"James" wrote in message ...
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" wrote in message ...
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" wrote in message ...
You are welcome



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



"James" wrote in message ...
Thanks again for the help
James
"Ron de Bruin" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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" wrote in message ...
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