VBA XLS 自动筛选条件不等于零，但我想测试公式的结果

VBA XLS Autofilter Criteria is Not equal to zero, but I'd like to test the result of a formula

提问by Brando

I'm trying to filter out all rows of a sheet where the result of column E (formula) is 0. I think I'm close. This sheet is the result of a pivot table on another sheet, and I want it to always take effect when the pivot is refreshed. Right now, the scope of the question is to display where Not 0, on a formula.

我正在尝试过滤掉 E 列（公式）的结果为 0 的工作表的所有行。我想我已经接近了。该工作表是另一个工作表上的数据透视表的结果，我希望它在刷新数据透视表时始终生效。现在，问题的范围是在公式上显示 where Not 0。

Sub HideIfZero()
  Dim LastRow As Long

  ActiveWorkbook.Sheets("Results").Activate 
  ActiveSheet.AutoFilterMode = False

  With Worksheets("Results")
    .Range("$A:$G").AutoFilter
    .Range("$A:$G").AutoFilter field:=5, Criteria1:="<>0" 'this isn't working in the NOT select

    LastRow = .Range("A" & .Rows.Count).End(xlUp).Row    
  End With 
End Sub

采纳答案by Jerome Montino

Try the following:

请尝试以下操作：

Sub HideIfZero()

    Dim PivotSht As Worksheet, WS As Worksheet
    Dim LastRow As Long, PT As PivotTable

    With ThisWorkbook
        Set PivotSht = .Sheets("ModifyMe") 'Modify to suit.
        Set WS = .ActiveSheet
    End With

    For Each PT In PivotSht.PivotTables
        PT.RefreshTable
    Next PT

    With WS
        .AutoFilterMode = False
        With .Range("A:G")
            .AutoFilter Field:=5, Criteria1:="<>0"
        End With
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Debug.Print LastRow
    End With

End Sub

This will update all pivot tables in the proper sheet and apply a filter in the worksheet where you want the filter.

这将更新正确工作表中的所有数据透视表，并在您想要过滤器的工作表中应用过滤器。

As mentioned by @andy holaday, this will work for both hard values and formula-calculated values that are equal to non-zero (your condition).

正如@andy holaday 所提到的，这适用于硬值和等于非零（您的条件）的公式计算值。

Let us know if this helps.

如果这有帮助，请告诉我们。