This sounds like it might be a better approach.....however, I am getting a
Compile error: syntax error on line:
If rng.Rows(i).Find(what:=PATTERN, lookat:=xlWhole) Is Nothing

R.

"Harlan Grove" wrote:

Robert wrote...
....
I don't beleive I can use infinite row designations (A:A) for an array
formula. I tried and get a #NUM error. Is there any way I can lock the
ranges on sheet 1 so they don't change?........or perhaps some other approach
to this problem?

You could either change your macro to COPY content up rather than
deleting rows.


Sub dontfubar()
Const PATTERN As String = "*COMPANY*" 'adjust to company name

Dim i As Long, j As Long, rng As Range

On Error GoTo CleanUp
Application.Calculation = xlCalculationManual

Set rng = Range("A:H") 'modify as needed - better to reduce this if
possible

i = 0
j = 0
Do While i < rng.Rows.Count
i = i + 1

If rng.Rows(i).Find(what:=PATTERN, lookat:=xlWhole) Is Nothing
Then
j = j + 1
rng.Rows(j).Value2 = rng.Rows(i).Value2
Else
i = i + 9
End If
Loop

i = rng.Rows.Count
If j < i Then rng.Range(rng.Rows(j + 1), rng.Rows(i)).ClearContents

CleanUp:
Application.Calculation = xlCalculationAutomatic

End Sub


Or you could rewrite your formulas to use fixed references like

INDEX(OtherSheet!$A:$A,1,1):INDEX(OtherSheet!$H:$H ,1000,1)

instead of

OtherSheet!$A$1:$H$1000