Worksheet类的ShowAllData方法失败 [英] ShowAllData method of Worksheet class failed

问题描述

我注意到，当有自动过滤器已经开启时，我的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.

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

The second catch Or ActiveSheet.FilterMode should catch advanced filters

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