我试着写一个脚本来应用过滤器,我试图从一个单元格中获取值,然后应用大于或等于。但我想我有一些语法。
Sub GTE()
'
' GTE Macro
'
'
Dim var As Double
var = Range("X1").Value
Selection.Copy
ActiveSheet.ListObjects("data").Range.AutoFilter Field:=10, Criteria1:= _
">=" + var, Operator:=xlAnd
End Sub
这就是您需要的所有代码。您不需要Selection.Copy
行就可以使过滤器工作。
Dim var As Double
var = Range("X1").Value
ActiveSheet.ListObjects("data").Range.AutoFilter Field:=10, Criteria1:= _
">=" & var, Operator:=xlAnd
一些建议:
- 为过程和变量指定有意义的名称
- 无需引用
ActiveSheet
(,除非您是认真的( - 您可以通过表的范围名称和附加的列表对象来引用表名
参考范围时,请尝试完全限定它们。
取而代之的是:
Range("X1").Value
使用此:
Thisworkbook.Worksheets("TheSheetName").Range("X1").Value
或者更好的是:
' Define and assign a worksheet variable
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets("TheSheetName")
' Define and assign the filter variable
Dim filterValue As Double
filterValue = targetSheet.Range("X1").Value
代码:
Public Sub FilterData()
' Define and assign a worksheet variable
Dim targetSheet As Worksheet
Set targetSheet = ThisWorkbook.Worksheets("TheSheetName")
' Define and assign the filter variable
Dim filterValue As Double
filterValue = targetSheet.Range("X1").Value
' Define and assign the table (listobject)
Dim dataTable As ListObject
Set dataTable = Range("data").ListObject
' Filter the table. No need to reference the activesheet
dataTable.Range.AutoFilter Field:=10, Criteria1:= _
">=" & filterValue, Operator:=xlAnd
End Sub