0
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?

SenthamilVM
  • 93
  • 1
  • 3
  • 13
  • 1
    Possible duplicate of [Selecting columns based on specific text strings](https://stackoverflow.com/questions/49118849/selecting-columns-based-on-specific-text-strings) – CallumDA Mar 08 '18 at 09:32
  • `.columns(worksheetfunction.match("Order Status",range("1:1"),false))` to check column in row 1 for example. – Nathan_Sav Mar 08 '18 at 09:54

1 Answers1

1

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