vba - 显示包含两个给定值之间的值的行

Question

我正在尝试创建一个相对复杂的 Excel 工作表，但遇到了这个问题。

所以我有一个数据表，其中包含建筑物名称、位置等，然后在D 列中我有该建筑物的面积大小。我有一个名为“控制”的单独工作表，它将允许用户通过下拉菜单控制第二个工作表的输出。我希望他们能够控制的一个区域是显示/隐藏包含特定大小建筑物的行。例如，我有一个尺寸列表，例如:

0-19,999
20,000-39,999
40,000-59,999
60,000-79,999
80,000-99,999
100,000+

相邻单元格中有显示/隐藏下拉菜单。如果我只为 80-99,999 和 100,000+ 选择“显示”，我只想能够在第二张纸上看到属于此类别的建筑物行。

我对 Visual Basic 有非常基本的了解，但我认为这是做这样的事情的唯一方法？

如有任何帮助，我们将不胜感激!我只需要一个开始，希望能够解决这个问题。

编辑:

按照 Jean-Pierre 的建议，我通过使用此代码获得了某种功能:

    If Range("F2").Value = "Yes" Then
Worksheets("Output").Range("$A$2:$AC$198").AutoFilter Field:=4, _
                                Criteria1:=">=0", _
                                Operator:=xlAnd, _
                                Criteria2:="<=19999"
ElseIf Range("F3").Value = "Yes" Then
Worksheets("Output").Range("$A$2:$AC$198").AutoFilter Field:=4, _
                                Criteria1:=">=20000", _
                                Operator:=xlAnd, _
                                Criteria2:="<=39999"
End If

但是，当在控制表中为两者都选择"is"时，输出仅显示面积大小在 0 到 19,999 之间的建筑物，而不是 0 到 39,999。如果我将两者都更改为“否”，那么它仍然显示 0 到 19,999。所以功能并不完全正确。有想法吗？

编辑2:

控制

输出

代码

Sub ShowBuildings()

Dim wsC As Worksheet, wsO As Worksheet    
Set wsC = Worksheets("Control")
Set wsO = Worksheets("Output")

Dim x As Long, sList() As String
x = 0

With wsC

    Dim rng As Range

For Each rng In .Range("F2:F13")

    If rng.Value2 = "Yes" Then

        ReDim Preserve sList(x)
        sList(x) = rng.Offset(, -1)

        x = x + 1

    End If

    Next

End With

With wsO
With .Range(.Range("D2"), .Range("D" & .Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:=sList, Operator:=xlFilterValues
End With
End With

End Sub

吉普代码

If Not Intersect(Target, Range("F2:F13")) Is Nothing Then
    On Error GoTo bm_Safe_Exit
    Application.EnableEvents = False
    Dim v As Long, vSQFTs As Variant
    vSQFTs = Range("E2:G13").Value2
    For v = LBound(vSQFTs, 1) To UBound(vSQFTs, 1)
        vSQFTs(v, 3) = CBool(LCase(vSQFTs(v, 2)) = "Yes")
        If v < UBound(vSQFTs, 1) Then
            vSQFTs(v, 2) = CLng(Split(vSQFTs(v, 1), Chr(45))(1))
            vSQFTs(v, 1) = CLng(Split(vSQFTs(v, 1), Chr(45))(0))
        Else
            vSQFTs(v, 1) = CLng(Split(vSQFTs(v, 1), Chr(43))(0))
            vSQFTs(v, 2) = Rows.Count  '1,048,576 make this larger if you want
        End If
    Next v
    buildingsShowHide vSQFTs
End If
bm_Safe_Exit:
Application.EnableEvents = True

End Sub


Sub buildingsShowHide(aSQFTs As Variant)
Dim a As Long, rw As Long, sqft As Long, sft As String, dSQFTs As Object

Application.ScreenUpdating = False
Set dSQFTs = CreateObject("Scripting.Dictionary")

With Worksheets("Output")
    If .AutoFilterMode Then .AutoFilterMode = False
    For rw = 3 To .Cells(Rows.Count, "D").End(xlUp).Row
        sqft = .Cells(rw, "D").Value2
        sft = .Cells(rw, "D").Text
        If Not dSQFTs.exists(sft) Then
            For a = LBound(aSQFTs, 1) To UBound(aSQFTs, 1)
                If sqft >= aSQFTs(a, 1) And sqft <= aSQFTs(a, 2) And      aSQFTs(a, 3) Then
                    dSQFTs.Add Key:=sft, Item:=sqft
                    Exit For
                End If
            Next a
        End If
    Next rw

    With .Columns(4)
        .AutoFilter Field:=1, Criteria1:=dSQFTs.keys,      Operator:=xlFilterValues
    End With
End With

dSQFTs.RemoveAll: Set dSQFTs = Nothing
Application.ScreenUpdating = True
End Sub

Answer 1

要执行您需要的操作，请使用以下命令:

If Option1 = True Then
    'Active sheet will become the sheet you want to filter
    'Field 9 refers to the 9th field of the filter range
    ActiveSheet.Range("$A$1:$M$3").AutoFilter Field:=9, _
                                    Criteria1:=">=0", _
                                    Operator:=xlAnd, _
                                    Criteria2:="<=19999"
ElseIf Option2 = True Then
    ActiveSheet.Range("$A$1:$M$3").AutoFilter Field:=9, _
                                    Criteria1:=">=20000", _
                                    Operator:=xlAnd, _
                                    Criteria2:="<=39999"
'Remainder of options here

End If