VBA：如何使用表格标题引用列，以便我可以在单元格引用中使用该列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21612518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: How to refer to columns using table headings so I can use that column in a cell reference
提问by SRoy
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
回答by Dmitry Pavliv
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
