Reference column range using column header instead of column number

Question

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?

Possible duplicate of Selecting columns based on specific text strings — CallumDA
– CallumDA, Commented Mar 8, 2018 at 9:32
.columns(worksheetfunction.match("Order Status",range("1:1"),false)) to check column in row 1 for example. — Nathan_Sav
– Nathan_Sav, Commented Mar 8, 2018 at 9:54

score 1 · Accepted Answer · 2018-03-08 10:01:56Z

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

Collectives™ on Stack Overflow

Reference column range using column header instead of column number

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related