VBA：如何使用表标题引用列，以便我可以在单元格引用中使用该列

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!

所以基本上我有一个代码，如果它们有两列符合某些条件，它们的目的是查找和删除行。但是，表中这些列的位置通常是可变的，因此我需要使用它们的表头名称来引用这两列。我有一种方法来使用列号来查找和删除这些行，但是将其调整为列名称的方式并不相同。如何调整我的代码才能使其正常工作？我甚至可以使用FIND功能吗？提前致谢！

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

1 个解决方案

#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

#1