vba - Excel VBA : How to create an autofilter with an exception to the filter?

Question

我是 VBA 新手，正在开发一个项目，在该项目中我必须过滤两个特定值(4400000000 和 5600000000)之间的数据。这对我来说比较棘手的是，5500000000 中的一些后面有字母，即 5500000000 CST。如果这些数字后面有字母，我该如何编写我的代码，使其包含这些数字。如果这太模糊了，我该如何编写代码才能包含 CST？这是到目前为止我的代码:

Sub macro5()
Worksheets("info1").Range("A1").AutoFilter _
Field:=7, _
Criteria1:=">=" & 4400000000#, Operator:=xlFilterValues, Criteria2:="<5600000000, Operator:=xlFilterValues"
End Sub 

Answer 1

循环并创建要过滤的值的字典，然后使用键作为 xlfiltervalues 的条件 1。

Option Explicit

Sub macro5()
    Dim d As Long, dict As Object
    Dim i As Double, mn As Double, mx As Double

    Set dict = CreateObject("scripting.dictionary")
    mn = 4400000000#
    mx = 5600000000#

        With Worksheets("info1")
            If .AutoFilterMode Then .AutoFilterMode = False
            For d = 2 To .Cells(.Rows.Count, "G").End(xlUp).Row
                If IsNumeric(Left(.Cells(d, "G").Value2, 1)) Then
                    i = CDbl(Split(.Cells(d, "G").Value2 & Chr(32), Chr(32))(0))
                    If i >= mn And i < mx Then
                        dict.Item(CStr(.Cells(d, "G").Value2)) = .Cells(d, "G").Value2
                    End If
                End If
            Next d
            .Range("A1").AutoFilter Field:=7, Criteria1:=dict.keys, Operator:=xlFilterValues
        End With
End Sub