Application Defined or object defined error - Run time error 1004

[vbvba]

Hi Guys,

I'am trying to write a small script to delete the "Cancelled" word in a column and keep the "Shipped" and "delivered" words.

Below is the code

Sub removecancelled()
Dim st As String, i As Integer, lastrow1 As Long
st = WorksheetFunction.Match("Status", Range("1:1"), 0)
lastrow1 = Cells(Rows.Count, st).End(xlUp).Row
For i = lastrow1 To 2 Step -1
If Cells(i, st) = "cancelled" Then
Cells(i, st).EntireRow.Delete
End If
Next i

However, when I run the script get an error "Application Defined or object defined error - Run time error 1004" in the 4th line

lastrow1 = Cells(Rows.Count, st).End(xlUp).Row

Please reply and help me in finding out the issue in this script.

 

[Norie]

Declare st as Long, not string.

 

[Rick Rothstein]

Here is a different method (no loops) that should work for you (if you have a lot of rows of data, then it should also be faster than the code you now have)...

Sub removecancelled()
  With Rows(1).Find("Status", , , xlWhole, , , True).EntireColumn
    .Replace "cancelled", "#N/A", xlWhole, , False
    On Error Resume Next
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[vbvba]

Hi Norie,

Thanks for the reply

I declared st as Long now the error is different "Unable to get the match property of the worksheetfunction class"

Kindly confirm what could be the issue now ?

 

[Norie]

That suggests 'Status' isn't being found.

 

[vbvba]

Hi Rick,

That worked like Gem thanks for the reply

 

[vbvba]

Hi Norie,

Oops Status was declared in 2nd row of the sheet so match function was unable to search it in first row.
Thanks for the help