CODEWITHSUNDEEP
excelvba
SenthamilVM
SenthamilVM

Reputation: 93

Reference column range using column header instead of column number

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

Answers (1)

user4039065
user4039065

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

Related Questions