Error 91 in using while loop with Cells.Find

[Raj007]

Hi am a basic learner in using VB. I created the following code using record macro. It does the job. However, it is giving error. Hence, I request you to take a look.

While (Cells.Find(What:="Data", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate And ActiveCell.Row <= ActiveSheet.UsedRange.Rows.Count)
Application.ScreenUpdating = True
Fnd = ActiveCell.Row
Frm = Fnd - 2
Too = Fnd + 5
Rows(Frm & ":" & Too).Select
Selection.Delete Shift:=xlUp
Cells(Frm, 1).Select
Wend

Appreciate your response. Thanks!

 

[Norie]

The criteria for the While loop isn't right.

What is your code meant to do?

What part of it is recorded?

 

[Raj007]

The criteria for the While loop isn't right.

What is your code meant to do?

What part of it is recorded?

Objective:
The code has to search for the name "Data"(in the first column). After it finds the cell that has "Data" in the i'th row, it selects rows from i-2 to i+5 and deletes the same.

 

[Norie]

Try this.

For I = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1

    Set rng = Range("A" & I)

    If rng.Value = "Data" Then
        rng.Offset(-2).Resize(8).Delete xlShiftUp
    End If

Next I

 

[Raj007]

Hi Norie,

Thanks!
It worked fine. However it is slow as I have more than 200000 lines of data in each file.

Thanks alot....