Worksheet 类的 ShowAllData 方法失败

本文介绍了Worksheet 类的 ShowAllData 方法失败的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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

    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$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

    rng.Copy

    Range("$BC$2:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial

非常感谢

推荐答案

AutoFilterMode 将在启用时为 True，无论是否实际将过滤器应用于特定列.发生这种情况时，ActiveSheet.ShowAllData 仍会运行，并抛出错误(因为没有实际过滤).

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.ShowAllData will still run, throwing an error (because there is no actual filtering).

我遇到了同样的问题，并使用了

I had the same issue and got it working with

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

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

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

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

The second catch Or ActiveSheet.FilterMode should catch advanced filters

这篇关于Worksheet 类的 ShowAllData 方法失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！