Help with a tricky column filter

[russelldt]

Hello,

I have the following macro button in place to filter, and unfilter column 5 (table 1) for quantities greater than 1. I have added another column 6, which has a 1 or 0. The macro button to filter and unfilter column 5 must only apply to rows with a 1 in Column6.

I can't quite figure this out.


Thanks




Sub FilterTable()

Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range

Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table1")


If tbl.AutoFilter.FilterMode Then

tbl.AutoFilter.ShowAllData
Else

Set rng = tbl.ListColumns(5).DataBodyRange
rng.AutoFilter Field:=1, Criteria1:="<>", Operator:=xlAnd
End If

End Sub

 

[ChrisFoster]

I may have misunderstood, but try this.

Sub FilterTable()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    
    Set ws = ActiveSheet
    Set tbl = ws.ListObjects("Table1")
    
    If tbl.AutoFilter.FilterMode Then
        tbl.AutoFilter.ShowAllData
    Else
        Set rng = tbl.ListColumns(5).DataBodyRange
        tbl.Range.AutoFilter Field:=5, Criteria1:=">1", Operator:=xlAnd, Criteria2:="=1"
    End If
End Sub

 

[russelldt]

Thanks Chris, it works when I "hide" the rows in column 5, but when I unhide the rows, all the rows reappear, including the ones in column 6 that I don't want to show (those with a 0)

 

[ChrisFoster]

Try:

Sub FilterTable()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    
    Set ws = ActiveSheet
    Set tbl = ws.ListObjects("Table1")
    
    If tbl.AutoFilter.FilterMode Then
        tbl.AutoFilter.ShowAllData
    Else
        ' Apply filter to columns 5 and 6
        tbl.Range.AutoFilter Field:=5, Criteria1:=">1"
        tbl.Range.AutoFilter Field:=6, Criteria1:="=1"
        
        ' Hide the rows where column 6 has a value of 0
        Dim i As Long
        For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
            If tbl.DataBodyRange.Cells(i, 6).Value = 0 Then
                tbl.DataBodyRange.Cells(i, 1).EntireRow.Hidden = True
            End If
        Next i
    End If
End Sub

 

[russelldt]

Sorry, same result. This is the before condition

this is filtered

then this when I unfilter