Excel Macro捕获选定的过滤条件 [英] Excel Macro to capture selected filter criteria

问题描述

请帮助您获取用户给出的条件，以便在excel中过滤数据，然后在宏中使用该条件附加额外的过滤条件.

please can you help to fetch what criteria user gave to filter the data in excel and then use that in macro to append extra filter condition.

到目前为止，我已经做了以下工作.如何建立动态条件，就像有多个条件一样，我们是否有任何功能可以捕捉条件然后追加?

i have done below till now. how to build the dynamic criteria, me like if there are multiple conditions, do we have any function to capture the criteria and then append ?

    With Rng.Parent.AutoFilter

    If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

    With .Filters(Rng.Column - .Range.Column + 1)
        If Not .On Then GoTo Finish
        Filter = .Criteria1
        Filter = Filter & " OR " & Cells(4, 10)
        Select Case .Operator
            Case xlAnd
                Filter = Filter & " AND " & .Criteria2
            Case xlOr
                Filter = Filter & " OR " & .Criteria2
        End Select
    End With

推荐答案

下面是一些简单的代码，可让您开始在VBA上对某些示例数据使用 .AutoFilter .请注意，可以使用多个条件来过滤所需的数据，并且可以根据需要动态更改过滤条件.动画的gif显示逐步浏览代码. r.select 语句仅用于帮助您理解，理解后应将其删除.过滤数据后，将选择一些过滤后的数据并将其复制到另一张纸上.当然，您将根据需要进行修改.

Here is some simple code to get you started using .AutoFilter with VBA on some sample data. Notice that multiple criteria are used to filter the data as you requested, and that you can dynamically change the filter criteria if you like. The animated gif shows stepping through the code. The r.select statement is just to help you understand, and should be removed once you understand. After filtering the data some of the filtered data is selected and copied to another sheet. Of course, you would modify as needed.

Option Explicit
Sub test()
Dim sh1 As Worksheet, sh2 As Worksheet, r As Range, filteredRange As Range
Dim chosenName As String
Const idCol = 1, nameCol = 2, cityCol = 4, chosenCity = "Denver"

chosenName = InputBox("What name to filter by?")
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
Set r = sh1.Range("A1")
sh1.Activate
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

    r.AutoFilter field:=nameCol, Criteria1:="=" & chosenName
    r.AutoFilter field:=cityCol, Criteria1:="=" & chosenCity
    Set r = sh1.AutoFilter.Range.Columns(idCol)
    Set r = r.Offset(1, 0).Resize(r.Rows.Count - 1, 1)
    Set r = r.SpecialCells(xlVisible)
r.Select
r.Copy
sh2.Activate
sh2.Range("A1").Select
sh2.Paste
End Sub

这篇关于Excel Macro捕获选定的过滤条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！