VBA: How to refer to columns using table headings so I can use that column in a cell reference

Question

So basically I have a code that aims to find and delete rows if they have two columns that meet certain conditions. However, the position of these columns in the table are often variable so I need to reference the two columns using their table header name. I had an approach to using the column numbers to find and delete these rows but adapting it to the column names didn't work the same way. How can I tweak my code to make it work? Could I possibly even use the FIND function instead? Thanks in advance!

Code:

 1 Sub try()
 2    
 3 ThisWorkbook.Sheets("report").Activate
 4 Last1 = Cells(Rows.Count, "A").End(xlUp).Row     
 5 For p = Last1 To 1 Step -1
 6   If (Cells(p, "Table1[type]").Text) = "active" And (Cells(p, "Table1[data]")) <> "" Then
 7       Cells(p, "A").EntireRow.Delete
 8   End If
 9 Next p
10 End Sub    

Answer 1

Try this one:

Sub try()
    Dim Last1 As Long
    Dim colType As Integer, colData As Integer

    With ThisWorkbook.Sheets("report")
        Last1 = .Cells(.Rows.Count, "A").End(xlUp).Row
        colType = .Range("Table1[type]").Column
        colData = .Range("Table1[data]").Column
        For p = Last1 To 1 Step -1            
            If .Cells(p, colType) = "active" And .Cells(p, colData) <> "" Then
                .Cells(p, "A").EntireRow.Delete
            End If
        Next p
    End With
End Sub

BTW, if your table have many rows, next code would be more efficient:

Sub test2()
    Dim rng As Range
    Dim i As Long

    With ThisWorkbook.Sheets("report").ListObjects("Table1")
        .Range.AutoFilter
        .Range.AutoFilter Field:=.ListColumns("type").Index, _
            Criteria1:="=active"
        .Range.AutoFilter Field:=.ListColumns("data").Index, _
            Criteria1:="<>"
        .Range.Offset(1).EntireRow.Delete
        .Range.AutoFilter
    End With
End Sub