Excel VBA-表的ShowAllData失败

问题描述

我有更长的VBA(另请参阅我以前的问题:.

I have this longer piece of VBA (see also my former question: https://stackoverflow.com/questions/53206850/copy-rows-which-match-criteria-into-two-or-more-different-sheets-in-excel-with-v).

一件特别的东西不能正常工作.我希望表格在执行模块中的先前操作后立即 ShowAllData .我到目前为止编写的这段代码，只要我来自表格的一个单元格，只要运行，它就可以正常工作.一旦我从工作表上的另一个地方运行 ，它就会不再显示所有数据.它一直挂在表的过滤模式下.

One particular piece doesn't work properly. I want a table to ShowAllData as soon as its former actions from the module have been taken.This code I made so far, works fine as long as I run it when I'm from one of the cells of the table. As soon as I run it from another place on the worksheet, it won't show all data again. It keeps hanging in the filtermode of the table.

   Sub CopyOrders()

'Sorting column STOCK in ORDERS from A-Z
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Clear
    Worksheets("Orders").ListObjects("Orders").Sort. _
        SortFields.Add2 Key:=Range("Orders[[#All],[STOCK]]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets("Orders").ListObjects("Orders").Sort
        .Apply
    End With

'Delete all rows from table NoStockOrders
On Error Resume Next
Worksheets("NoStockOrders").ListObjects("NoStockOrders").DataBodyRange.EntireRow.Delete

'Copy all orders which have no stock to
'the sheet NoStockOrders
Worksheets("Orders").Range("ORDERS").AutoFilter _
    Field:=6, Criteria1:="0", VisibleDropDown:=True
On Error Resume Next
Worksheets("Orders").Range("ORDERS").SpecialCells _
    (xlCellTypeVisible).Copy
    Range("NoStockOrders").PasteSpecial _
    Paste:=xlPasteValues

If Worksheets("Orders").ListObjects("Orders").FilterMode Then
    Worksheets("Orders").AutoFilter.ShowAllData
End If

推荐答案

应为:

Worksheets("Orders").ListObjects("Orders").Autofilter.showalldata

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