Tom

Thanks for your reply. I've had a look at the FindNext
method in VBA and whilst understanding the demo with the
find value of 2, I'm struggling re-write for my example.
Can you kindly help? I'm not testing for any formatting,
just string values of "Other", "Belfast" and "Disc" in
cells i, 7. I'm not sure how to insert more than one row
at a time either. I'm used to coding EntireRow.Insert but
how do I ask VBA to insert 3 rows for "Other", 4 rows
for "Belfast" yet delete a row if it's "Disc". Sorry
but can't get my head around this one.

Many thanks
Jacqui

-----Original Message-----
See the excel VBA help on the findnext method. The
sample code shows you
how to do this for values in a cell. If you want to
check formatting only,
then you need to loop in xl2000 and earlier (at least)

--
Regards,
Tom Ogilvy

"jacqui" wrote in
message
...
Can anyone kindly recommend the quickest way to insert
rows based on specific criteria. For example I have VBA
that inserts a row when the loop finds that a cell in
the
range is formatted to Bold but is not Italic. I do this
with the following
Sub format_InsertRows()

Dim lngLastRow As Long
Dim i As Long

With Worksheets("data")
lngLastRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = lngLastRow To 2 Step -1
If .Cells(i, 6).Font.Bold And _
Not .Cells(i, 6).Font.Italic Then
.Cells(i + 1, 6).EntireRow.Insert
End If
Next

End With
End Sub


Now I need to be more specific and say if the contents
of
cells i, 7 = string value "Other" then insert 3 rows
above
it and if cells i, 7 = "Belfast" then insert 4 rows
above
it. I also need to say if cells i, 7 = "Disc" then
delete
3 rows beneath it. Given that the datafile contains
about
3000 rows in i, I wondered if it might be quicker to do
an
Edit/Find or a Do Until thing. Can anyone suggest the
quickest way and provide the syntax to do the above. My
methods may seem a bit crazy but I need to make the
datafile the same shape as a template to copy/paste the
data into. NB: Making my source data the same shape as
the template is not an option, Excel needs to do the
work
unfortunately.
Many thanks
Jacqui



.