Compile Error- Sub or Function not defined

[Vaghela]

Not sure what is going wrong with the following code, I am getting the Compile Error- Sub or Function not defined.

I am trying to delete all rows which dont have Cell value Ed.


Sub Macro1()

Dim r As Long
Dim FRow As Long
Dim LRow As Long

FRow = 2
LRow = 1000

For r = LRow To FRow Step -1
If Cell(r, 3) <> "Ed" Then
Rows("r").Select
Selection.Delete
End If
Next r

End Sub

 

[steve the fish]

Cells not Cell

 

[lrobbo314]

You're missing an 's' for the line 'If Cell(r,3)....

Should be like this.

Sub Macro1()


Dim r As Long
Dim FRow As Long
Dim LRow As Long


FRow = 2
LRow = 1000


For r = LRow To FRow Step -1
If Cells(r, 3) <> "Ed" Then
Rows("r").Select
Selection.Delete
End If
Next r


End Sub

 

[steve the fish]

And while you are here that's dangerous code. It doesnt specify a worksheet to work on. If inadvertently used on the wrong sheet it will basically delete everything. Specify your worksheets.

 

[WaterGypsy]

Assuming you are looking for "Ed" in col C, try

If Range("c" &r).Value <> "Ed" Then

 

[lrobbo314]

There are some more corrections too. Don't use 'Select'. Also the 'r' should not be in double quotes.

Sub Macro1()


Dim r As Long
Dim FRow As Long
Dim LRow As Long


FRow = 2
LRow = 1000


For r = LRow To FRow Step -1
    If Cells(r, 3) <> "Ed" Then
        Rows(r).Delete
    End If
Next r


End Sub

 

[Vaghela]

And while you are here that's dangerous code. It doesnt specify a worksheet to work on. If inadvertently used on the wrong sheet it will basically delete everything. Specify your worksheets.

how to define a worksheet, what if I plan to run the code with the relevant worksheet active.

 

[Vaghela]

thanks for the quick response, but why using Select is not a good option,

just trying to develop some knowledge around coding.