Insert Row Condition

[James]

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

 

[Ron de Bruin]

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

 

[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]

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

 

[James]

Thanks again for the 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

 

[Ron de Bruin]

You are welcome

 

[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

 

[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

 

[Ron de Bruin]

Hi James

I look at it tomorrow ( I must go now)

 

[James]

That's great, anytime really
Appriciate the help
James

 

[Ron de Bruin]

Hi James

If I understand you correct?

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

 

[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]

Hi James

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