blocks|key|2040508|text|如果使用Worksheet.AutoFilter.ShowAllData而不是Worksheet.ShowAllData，则不会在未筛选任何内容时抛出错误。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2040509|这假设为Worksheet.AutoFilterMode+=+True，因为否则您将得到一个关于AutoFilter不是对象的错误。|2040510|Public+Sub+UnFilter_DB()
Dim+ActiveS+As+String,+CurrScreenUpdate+As+Boolean

CurrScreenUpdate+=+Application.ScreenUpdating
Application.ScreenUpdating+=+False
ActiveS+=+ActiveSheet.Name

++++Sheets("DB").Activate
++++Sheets("DB").Range("A1").Activate
++++Sheets("DB").AutoFilter.ShowAllData
++++DoEvents
++++Sheets(ActiveS).Activate

Application.ScreenUpdating+=+CurrScreenUpdate
End+Sub|code-block|syntax|javascript|2040511|entityMap^0|4|W|13|L|0|4|V|1C|A|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|Y|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

If you use <code>Worksheet.AutoFilter.ShowAllData</code> instead of <code>Worksheet.ShowAllData</code> it will not throw the error when nothing is filtered.

This assumes that <code>Worksheet.AutoFilterMode = True</code> because otherwise you will get an error about <code>AutoFilter</code> not being an object.

<pre><code>Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean

CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name

 Sheets("DB").Activate
 Sheets("DB").Range("A1").Activate
 Sheets("DB").AutoFilter.ShowAllData
 DoEvents
 Sheets(ActiveS).Activate

Application.ScreenUpdating = CurrScreenUpdate
End Sub
</code></pre>

blocks|key|1756188|text|下面是我使用的工作解决方案：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|1756189|Public+Sub+UnFilter_DB()
Dim+ActiveS+As+String,+CurrScreenUpdate+As+Boolean

CurrScreenUpdate+=+Application.ScreenUpdating
Application.ScreenUpdating+=+False
ActiveS+=+ActiveSheet.Name

++++Sheets("DB").Activate
++++Sheets("DB").Range("A1").Activate
++++On+Error+Resume+Next
++++If+Sheets("DB").FilterMode+=+True+Then+Sheets("DB").ShowAllData
++++On+Error+GoTo+0

++++DoEvents
++++Sheets(ActiveS).Activate

Application.ScreenUpdating+=+CurrScreenUpdate
End+Sub|code-block|syntax|javascript|1756190|此外，我还编写了一个更有效的版本，以便在命名为Ranges的情况下易于重用：|1756191|如何使用：|1756192|Private+Sub+TEST_UnFilter_Table()
++++Dim+tB+As+Workbook,+_
++++++++Sh+As+Worksheet

++++Set+tB+=+ThisWorkbook
++++Set+Sh+=+tB.Sheets("DB")

++++Call+UnFilter_Table(Sh,+"Db_Val")
End+Sub|1756193|和专用函数进行优化(如果您有大表的话)：|1756194|Public+Function+UnFilter_Table(ByRef+SheetWithTable+As+Worksheet,+ByVal+RangeName+As+String)+As+Boolean

On+Error+GoTo+ErrHdlr
++++Dim+aWB+As+Workbook,+_
++++++++ActiveSH+As+Worksheet,+_
++++++++ScreenUpdateState+As+Boolean,+_
++++++++StatusBarState+As+Boolean,+_
++++++++CalcState+As+XlCalculation,+_
++++++++EventsState+As+Boolean,+_
++++++++DisplayPageBreakState+As+Boolean

++++Set+aWB+=+ActiveWorkbook
++++Set+ActiveSH+=+aWB.ActiveSheet

++++DisplayPageBreakState+=+ActiveSH.DisplayPageBreaks
++++ActiveSH.DisplayPageBreaks+=+False

++++With+Application
++++++++ScreenUpdateState+=+.ScreenUpdating
++++++++StatusBarState+=+.DisplayStatusBar
++++++++CalcState+=+.Calculation
++++++++EventsState+=+.EnableEvents

++++++++.ScreenUpdating+=+False
++++++++.DisplayStatusBar+=+False
++++++++.Calculation+=+xlCalculationManual
++++++++.EnableEvents+=+False
++++End+With


++++SheetWithTable.Activate
++++SheetWithTable.Range(RangeName).Cells(1,+1).Activate
On+Error+GoTo+0

On+Error+Resume+Next
++++If+SheetWithTable.FilterMode+Then+SheetWithTable.ShowAllData
On+Error+GoTo+0

On+Error+GoTo+ErrHdlr
++++DoEvents
++++ActiveSH.Activate
++++ActiveSH.DisplayPageBreaks+=+DisplayPageBreakState

++++With+Application
++++++++.ScreenUpdating+=+ScreenUpdateState
++++++++.DisplayStatusBar+=+StatusBarState
++++++++.Calculation+=+CalcState
++++++++.EnableEvents+=+EventsState
++++End+With

++++UnFilter_Table+=+True
On+Error+GoTo+0

Exit+Function
ErrHdlr:
UnFilter_Table+=+False
Debug.Print+"Error+in+unfiltering+sheet+"+&+SheetWithTable.Name+&+"+!"+&+vbCrLf+&+_
++++++++++++"Error+n°+"+&+Err.Number+&+vbCrLf+&+_
++++++++++++Err.Description
End+Function|1756195|entityMap^0|7|6|0|0|E|2|0|0|0|1|4|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|S|3|T|5|H|7|18|8|@]|D|@]|E|$I|J]]|$1|U|3|-4|5|6|7|19|8|@]|D|@]|E|$]]]|V|$]]

Here is the working solution I went with :

<pre><code>Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean

CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name

 Sheets("DB").Activate
 Sheets("DB").Range("A1").Activate
 On Error Resume Next
 If Sheets("DB").FilterMode = True Then Sheets("DB").ShowAllData
 On Error GoTo 0

 DoEvents
 Sheets(ActiveS).Activate

Application.ScreenUpdating = CurrScreenUpdate
End Sub
</code></pre>

<hr>

And a more efficient version I wrote to be reusable easily with Named Ranges :

How to use it :

<pre><code>Private Sub TEST_UnFilter_Table()
 Dim tB As Workbook, _
 Sh As Worksheet

 Set tB = ThisWorkbook
 Set Sh = tB.Sheets("DB")

 Call UnFilter_Table(Sh, "Db_Val")
End Sub
</code></pre>

And the proper function with optimisation (if you have big tables) :

<pre><code>Public Function UnFilter_Table(ByRef SheetWithTable As Worksheet, ByVal RangeName As String) As Boolean

On Error GoTo ErrHdlr
 Dim aWB As Workbook, _
 ActiveSH As Worksheet, _
 ScreenUpdateState As Boolean, _
 StatusBarState As Boolean, _
 CalcState As XlCalculation, _
 EventsState As Boolean, _
 DisplayPageBreakState As Boolean

 Set aWB = ActiveWorkbook
 Set ActiveSH = aWB.ActiveSheet

 DisplayPageBreakState = ActiveSH.DisplayPageBreaks
 ActiveSH.DisplayPageBreaks = False

 With Application
 ScreenUpdateState = .ScreenUpdating
 StatusBarState = .DisplayStatusBar
 CalcState = .Calculation
 EventsState = .EnableEvents

 .ScreenUpdating = False
 .DisplayStatusBar = False
 .Calculation = xlCalculationManual
 .EnableEvents = False
 End With


 SheetWithTable.Activate
 SheetWithTable.Range(RangeName).Cells(1, 1).Activate
On Error GoTo 0

On Error Resume Next
 If SheetWithTable.FilterMode Then SheetWithTable.ShowAllData
On Error GoTo 0

On Error GoTo ErrHdlr
 DoEvents
 ActiveSH.Activate
 ActiveSH.DisplayPageBreaks = DisplayPageBreakState

 With Application
 .ScreenUpdating = ScreenUpdateState
 .DisplayStatusBar = StatusBarState
 .Calculation = CalcState
 .EnableEvents = EventsState
 End With

 UnFilter_Table = True
On Error GoTo 0

Exit Function
ErrHdlr:
UnFilter_Table = False
Debug.Print "Error in unfiltering sheet " &amp; SheetWithTable.Name &amp; " !" &amp; vbCrLf &amp; _
 "Error n° " &amp; Err.Number &amp; vbCrLf &amp; _
 Err.Description
End Function
</code></pre>

blocks|key|2040571|text|With+ActiveSheet
+++++If+.AutoFilterMode+=+False+Then+.Cells(1,+1).AutoFilter
+++++For+Each+f+In+.AutoFilter.Filters
++++++++++If+f.On+Then+.ShowAllData:+Exit+For
Next|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2040572|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>With ActiveSheet
 If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
 For Each f In .AutoFilter.Filters
 If f.On Then .ShowAllData: Exit For
Next
</code></pre>

I have automated a proper record input into the table that I use as a database, and when the table is filtered the input don't work.

So I have code this to unfilter DataBase before every record input.

<pre><code>Public Sub UnFilter_DB()
Dim ActiveS As String, CurrScreenUpdate As Boolean

CurrScreenUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
ActiveS = ActiveSheet.Name

 Sheets("DB").Activate
 Sheets("DB").Range("A1").Activate
 Sheets("DB").ShowAllData
 DoEvents
 Sheets(ActiveS).Activate

Application.ScreenUpdating = CurrScreenUpdate
End Sub
</code></pre>

But now, it stays stuck on <code>Sheets("DB").ShowAllData</code> saying :

<blockquote>
 ShowAllData method of Worksheet Class failed
</blockquote>

because the table is already unfiltered... 

And I don't know if it is better to use an error handler like <code>On Error Resume Next</code> or how to detect if there is a filter or none.

Any pointers would be welcome!

Excel VBA - ShowAllData method of Worksheet Class failed

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我已经将适当的记录输入自动化到我用作数据库的表中，而当对表进行过滤时，输入就不能工作了。因此，在每次输入记录之前，我都要对DataBase进行反筛选。Public Sub UnFilter_DB()Dim ActiveS As String, CurrScreenUpdate As BooleanCurrScreenU...

问工作表类的Excel VBA - ShowAllData方法失败
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问工作表类的Excel VBA - ShowAllData方法失败EN