vba Worksheet 类的 ShowAllData 方法失败

ShowAllData method of Worksheet class failed

提问by Kris Van den Bergh

I notice my VBA script doesn't work when there's an autofilter already on. Any idea why this is?

我注意到我的 VBA 脚本在已经打开自动过滤器时不起作用。知道这是为什么吗？

    wbk.Activate
    Set Criteria = Sheets("Sheet1").Cells(i, 1)

    Set rng = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, 4))

    wb.Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter, but it crashes on this line

    Selection.AutoFilter

    Range("$A:$BM4").AutoFilter Field:=2, Criteria1:=Criteria.Value

    rng.Copy

    Range("$BC:$BE4").SpecialCells(xlCellTypeVisible).PasteSpecial

Many thanks

非常感谢

回答by Aaron

AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllDatawill still run, throwing an error (because there is no actual filtering).

如果启用 AutoFilterMode 将为 True，无论是否实际应用到特定列的过滤器。发生这种情况时，ActiveSheet.ShowAllData仍会运行，抛出错误（因为没有实际过滤）。

I had the same issue and got it working with

我有同样的问题，并得到它的工作

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

This seems to prevent ShowAllData from running when there is no actual filter applied but with AutoFilterMode turned on.

这似乎阻止 ShowAllData 在没有应用实际过滤器但打开 AutoFilterMode 时运行。

The second catch Or ActiveSheet.FilterModeshould catch advanced filters

第二个捕获Or ActiveSheet.FilterMode应捕获高级过滤器

回答by Steven Martin

The simple way to avoid this is not to use the worksheet method ShowAllData

避免这种情况的简单方法是不使用工作表方法 ShowAllData

Autofilter has the same ShowAllData method which doesn't throw an error when the filter is enabled but no filter is set

Autofilter 具有相同的 ShowAllData 方法，该方法在启用过滤器但未设置过滤器时不会引发错误

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

回答by Martin

The error ShowAllData method of Worksheet class failedusually occurs when you try to remove an applied filter when there is not one applied.

ShowAllData method of Worksheet class failed当您尝试删除未应用的过滤器时，通常会发生该错误。

I am not certain if you are trying to remove the whole AutoFilter, or just remove any applied filter, but there are different approaches for each.

我不确定您是要删除整个AutoFilter，还是只是删除任何应用的过滤器，但每种方法都有不同的方法。

To remove an applied filter but leave AutoFilteron:

要删除已应用的过滤器但保持AutoFilter打开状态：

If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

The rationale behind the above code is to test that there is an AutoFilteror whether a filter has been applied (this will also remove advanced filters).

上述代码背后的基本原理是测试AutoFilter是否已应用过滤器（这也将删除高级过滤器）。

To completely remove the AutoFilter:

要完全删除AutoFilter：

ActiveSheet.AutoFilterMode = False

In the above case, you are simply disabling the AutoFiltercompletely.

在上述情况下，您只是完全禁用AutoFilter。

回答by Peer Sommerlund

I have just experienced the same problem. After some trial-and-error I discovered that if the selection was to the right of my filter area AND the number of shown records was zero, ShowAllData would fail.

我刚刚遇到了同样的问题。经过反复试验，我发现如果选择位于过滤器区域的右侧并且显示的记录数为零，则 ShowAllData 将失败。

A little more context is probably relevant. I have a number of sheets, each with a filter. I would like to set up some standard filters on all sheets, therefore I use some VBA like this

更多的上下文可能是相关的。我有很多张纸，每张纸都有一个过滤器。我想在所有工作表上设置一些标准过滤器，因此我使用了一些这样的 VBA

Sheets("Server").Select
col = Range("1:1").Find("In Selected SLA").Column
ActiveSheet.ListObjects("Srv").Range.AutoFilter Field:=col, Criteria1:="TRUE"

This code will adjust the filter on the column with heading "In Selected SLA", and leave all other filters unchanged. This has the unfortunate side effect that I can create a filter that shows zero records. This is not possible using the UI alone.

此代码将调整标题为“In Selected SLA”的列上的过滤器，并保持所有其他过滤器不变。这有一个不幸的副作用，我可以创建一个显示零记录的过滤器。单独使用 UI 是不可能的。

To avoid that situation, I would like to reset all filters before I apply the filtering above. My reset code looked like this

为了避免这种情况，我想在应用上述过滤之前重置所有过滤器。我的重置代码看起来像这样

Sheets("Server").Select
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Note how I did not move the selected cell. If the selection was to the right, it would not remove filters, thus letting the filter code build a zero-row filter. The second time the code is run (on a zero-row filter) ShowAllData will fail.

请注意我没有移动选定的单元格。如果选择在右侧，则不会删除过滤器，从而让过滤器代码构建零行过滤器。第二次运行代码（在零行过滤器上）ShowAllData 将失败。

The workaround is simple: Move the selection inside the filter columns before calling ShowAllData

解决方法很简单：在调用 ShowAllData 之前移动筛选列内的选择

Application.Goto (Sheets("Server").Range("A1"))
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

This was on Excel version 14.0.7128.5000 (32-bit) = Office 2010

这是在 Excel 版本 14.0.7128.5000（32 位）= Office 2010 上

回答by BobbyA

This will work. Define this, then call it from when you need it. (Good for button logic if you are making a clear button):

这将起作用。定义它，然后在需要时调用它。（如果您要制作清晰的按钮，则适用于按钮逻辑）：

Sub ResetFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

回答by Lee Li Fong

I am also the same problem. I think the reason are,

我也是同样的问题。我认为原因是，

1) When my activecell is within the table, "ActiveSheet.ShowAllData" can be work. 2) When my activecell not within the table, "ActiveSheet.ShowAllData" cannot work.Using this code, ActiveSheet.ListObjects("Srv").Range.AutoFilter Field:=1 can clear the filter.

1）当我的活动单元在表格内时，“ActiveSheet.ShowAllData”可以工作。2）当我的activecell不在表格内时，“ActiveSheet.ShowAllData”不能工作。使用这个代码，ActiveSheet.ListObjects("Srv").Range.AutoFilter Field:=1可以清除过滤器。