Reputation: 93
Sub Test1()
Dim LastRow As Range
Dim cfind As Range
'Set WS = ActiveWorkbook.Worksheets("Report")
'With WS
'Set cfind = Cells.Find(what:="Order Status", lookat:=xlWhole, MatchCase:=False)
'End With
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
For i = LastRow To 2 Step -1
If Range("C" & i).Value = "Canceled" Then
Range("C" & i).EntireRow.Delete
End If
Next i
End Sub
I am trying to delete the rows which has a value "Canceled" in a column which has a header "Order Status". I am currently using the column number or name. I am not sure how to use column header (Order Status) to delete the rows. Can someone help me?
Upvotes: 0
Views: 94
Reputation:
Your LastRow was a range object, should have been a long.
Sub Test1()
'not necessary now but should have been a long
'Dim LastRow As long
'not necessary now
'Dim cfind As Range
Dim col As Variant
With ActiveWorkbook.Worksheets("Report")
col = Application.Match("Order Status", .Rows(1), 0)
If Not IsError(col) Then
For i = .Cells(.Rows.Count, col).End(xlUp).Row To 2 Step -1
If .Cells(i, col).Value = "Canceled" Then
.Rows(i).EntireRow.Delete
End If
Next i
else
msgbox "no 'Order Status' here"
End If
end with
End Sub
Upvotes: 1