Excel VBA - 使用命名范围的自定义过滤器

Excel VBA - Custom Filter using Named Range

提问by Mus

I have a column of data on a worksheet, contained within a very large workbook that I am working on, named Vendorand I want to write a macro in VBA which allows me to filter by a particular vendor, say Vendor A.

我在工作表上有一列数据，包含在我正在处理的一个非常大的工作簿中，命名Vendor并且我想在 VBA 中编写一个宏，它允许我按特定供应商进行过滤，例如Vendor A.

So far I have this:

到目前为止，我有这个：

Sub filterVendor()
'
' filterVendor Macro
'

'
    ActiveSheet.Range("$A:$BB").AutoFilter Field:=21, Criteria1:= _
        "Vendor A"

End Sub

The problem I have is that the location of the Vendorcolumn may well change - as other columns are added and/or deleted before it, causing it to shift accordingly - and I want to know if it is possible to reference this particular column/block of data to save the macro becoming useless.

我遇到的问题是该Vendor列的位置可能会发生变化 - 因为在它之前添加和/或删除了其他列，导致它相应地移动 - 我想知道是否可以引用此特定列/块数据来保存宏变得无用。

Ideally, I would reference the range (vendoris what I would like to call the named range) so that, no matter what happens to the structure of the worksheet, the filter will always work correctly when the macro is run.

理想情况下，我会引用范围（vendor我想称之为命名范围），这样，无论工作表的结构发生什么变化，当宏运行时过滤器将始终正常工作。

How can this be done?

如何才能做到这一点？

FURTHER INFO:

更多信息：

I suspect that the Field:criteria is causing the problem as this appears to absolutely define the column to be filtered; even though I have specified that the Vendorcolumn is named vendoras a named range, and even though the named range reference moves along with said Vendorcolumn, the column that is filtered always lies at the position specified in Field:. How can this be fixed in order to work how I want it to?

我怀疑是Field:标准导致了问题，因为这似乎绝对定义了要过滤的列；即使我已指定该Vendor列被命名vendor为命名范围，并且即使命名范围引用与所述Vendor列一起移动，被过滤的列始终位于Field:. 如何解决这个问题才能按照我想要的方式工作？

I have tried:

我试过了：

...Field:=Range("vendor")

and

和

...Field:=(Range("vendor"))

but both of these cause an error.

但这两者都会导致错误。

采纳答案by Dan Wagner

Interesting challenge! I tackled this by:

有趣的挑战！我通过以下方式解决了这个问题：

• Using a named Range, "Vendor", as you describe (I think)
• Writing a small helper function, DoesVendorExist, that ensures the named Range"Vendor" exists
• Writing another small helper function, VendorColNum, that returns the column number of "Vendor"
• Applying the .Autofilterbased on the number returned by VendorColNum

• 使用命名的Range“供应商”，正如您所描述的（我认为）
• 编写一个小的辅助函数，DoesVendorExist以确保Range存在命名的“供应商”
• 编写另一个小辅助函数，VendorColNum返回“供应商”的列号
• .Autofilter根据返回的数字应用VendorColNum

Here is a screenshot of the (admittedly small) workbook I tested on:

这是我测试的（不可否认的小）工作簿的屏幕截图：

Here are the helper functions and a slightly-modified filterVendor2subroutine:

以下是辅助函数和稍微修改的filterVendor2子程序：

Option Explicit
Sub filterVendor2()

Dim DataSheet As Worksheet
Dim VendorCol As Long

'set references up-front
Set DataSheet = ThisWorkbook.Worksheets("data")

'...
'doing other stuff
'...

'here we make sure a named range called Vendor exists
If Not DoesVendorExist(ThisWorkbook) Then
    MsgBox ("Dangit! There is not a named range Vendor in this workbook! Exiting...")
    Exit Sub
End If

'define the vendor column
VendorCol = VendorColNum(DataSheet)

'here we make sure the Vendor named range is not empty
If VendorCol = 0 Then
    MsgBox ("Bummer! The named range Vendor was Nothing on DataSheet, exiting...")
    Exit Sub
End If

'otherwise, let's do some filtering!
DataSheet.Range("$A:$BB").AutoFilter Field:=VendorCol, Criteria1:="SnookerFan"

'...
'doing other cool stuff and finishing up
'...

End Sub

'INPUT  : pass this function the worksheet on which the Vendor range is defined
'OUTPUT : the column number that the Vendor range exists in
'SPECIAL: if the Vendor Range is nothing, return 0
Public Function VendorColNum(Sheet As Worksheet) As Long

'if the Vendor range is missing, return 0 and exit
If Sheet.Range("Vendor") Is Nothing Then
    VendorColNum = 0
    Exit Function
End If

'otherwise, we return a real column number
VendorColNum = Sheet.Range("Vendor").Column

End Function

'INPUT  : pass this function the workbook we'd like to check for Vendor
'OUTPUT : true/false... does Vendor exist?
'SPECIAL: none
Public Function DoesVendorExist(Book As Workbook) As Boolean

Dim Nm As Name

'assume false, test each name and flip the flag if Vendor is found
'assume false, test each name and flip the flag if Vendor is found
DoesVendorExist = False
For Each Nm In Book.Names
    If UCase(Nm.Name) = UCase("vendor") Then '<~ error was here, did not paste over .Name
        DoesVendorExist = True
        Exit Function
    End If
Next Nm

End Function

Finally, this is the resulting filtered Range. You should find that adding or removing columns on the data sheet does not wreck the code.

最后，这是过滤后的结果Range。您应该会发现在数据表上添加或删除列不会破坏代码。

Edit:my apologies as the error on checking the named range was a sloppy copy/paste as I worked through the issue myself. I have updated the code above and highlighted the error (was checking Nmto see if it matched "Vendor", should have been checking Nm.Nameto see if it matched UCASE("vendor").

编辑：我很抱歉，因为在我自己解决问题时检查命名范围的错误是草率的复制/粘贴。我已经更新了上面的代码并突出显示了错误（正在检查Nm它是否与“供应商”匹配，应该检查Nm.Name它是否与UCASE("vendor").

回答by Gary's Student

Autofilterlikes one-based single-dimensioned arrays, so we build and apply this type of array:

Autofilter喜欢基于一维的数组，所以我们构建并应用这种类型的数组：

Sub FilterMyData()
    Dim ary(), N As Long, i As Long

    N = Range("Vendors").Count
    ReDim ary(1 To N)
    For i = 1 To N
        ary(i) = Range("Vendors")(i).Value
    Next i

    With ActiveSheet.Range("A:BB")
        .AutoFilter Field:=23, Criteria1:=ary, Operator:=xlFilterValues
    End With
End Sub

回答by Othya

Are you using MS Excel version 2010? If so, you can use the built-in filter option, which can be used ad-hoc.

您使用的是 MS Excel 2010 版吗？如果是这样，您可以使用内置过滤器选项，该选项可以临时使用。

First, you'll need to form a table.. then just click the filter arrows.

首先，您需要形成一个表格...然后只需单击过滤器箭头即可。