Geek asks and answers

VBA: how to access columns using table headers so I can use that column in a cell reference

So basically I have code that is designed to find and delete rows if they have two columns that satisfy certain conditions. However, the position of these columns in a table is often variable, so I need to refer to two columns using their table header name. I had an approach to using column numbers to find and delete these rows, but adapting it to column names did not work the same. How can I customize my code to work? Can I even use the FIND function? Thanks in advance!

the 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
+3
source share
1 answer

Try the following:

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

, , :

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
+8

More articles:


All Articles