How to filter results of previous filters using advanced filters

Copper Contributor

May 30, 2023

Thanks again! Everything was double checked and it is ok:

1. Ensure that the table name ("TableGO") is correct and matches the name of your table in the worksheet "SIIPP". --- OK, the name is correct.

2. Double-check the range references for the filter criteria (e.g., "O3:O4", "P3:P4", etc.) to ensure they are valid and correspond to the correct criteria range in your worksheet. --- OK, they are correct!

3. Verify that the table range being filtered ("TableGO[#All]") covers the entire table range, including headers and data. --- My TableGO has the range "B36:IB569". The columns the advanced filter uses are in the range O-->IB. From columns B-->N, there is information that should be shown when the filter is applied.

4. Make sure there are no merged cells within the table range or the criteria range, as this can cause issues with the AdvancedFilter method. ---OK, there are no merged cells within the table range or the criteria range

5. Check if the worksheet "SIIPP" is protected or locked. If it is, temporarily remove the protection to see if it resolves the error. --- OK, it is not protected or locked.

Ensure that there is enough space in the worksheet for the filtered results to be copied. If the table range or filtered results exceed the available space, it can cause the AdvancedFilter method to fail.
--- OK, I think that space is not the problem, since TableGO is a short table, still!

NikolinoDE

Gold Contributor

May 30, 2023

carloscmferreira0704

Please try that...before I get confused :))

Sub LimpaFiltro()
' LimpaFiltro Macro
    ActiveSheet.ShowAllData
End Sub

Sub Filtro()
' Filtro Macro
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Remove any existing filters and show all data
        .ShowAllData
        ' Apply the first filter
        .ListObjects("TableGO").Range.AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("O3:O4"), _
            Unique:=False
        ' Copy the visible cells to another location
        Set rng = .ListObjects("TableGO").DataBodyRange.SpecialCells(xlCellTypeVisible)
        rng.Copy Destination:=.Range("W1")
    End With
End Sub

Sub FiltroDE()
' Filtro Macro
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Apply the second filter to the copied data in column W
        .Range("W:W").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("P3:P4"), _
            Unique:=False
    End With
End Sub

Sub FiltroAP()
' Filtro Macro
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Apply the third filter to the copied data in column W
        .Range("W:W").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("Q3:Q4"), _
            Unique:=False
    End With
End Sub

Sub FiltroODS()
' Filtro Macro
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Apply the fourth filter to the copied data in column W
        .Range("W:W").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("R3:R4"), _
            Unique:=False
    End With
End Sub

Sub FiltroPEDS()
' Filtro Macro
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Apply the fifth filter to the copied data in column W
        .Range("W:W").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("S3:S4"), _
            Unique:=False
    End With
End Sub

Sub FiltroFonte()
' Filtro Macro
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Apply the sixth filter to the copied data in column W
        .Range("W:W").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("T3:T4"), _
            Unique:=False
    End With
End Sub

Sub FiltroIP()
' Filtro Macro    
    Dim rng As Range
    With ThisWorkbook.Worksheets("SIIPP")
        ' Apply the seventh filter to the copied data in column W
        .Range("W:W").AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("U3:U4"), _
            Unique:=False
    End With
End Sub

After running these modified macros in sequence (e.g., Filtro, then FiltroDE, then FiltroAP, etc.), each subsequent filter will be applied to the results of the previous filter.

I hope this helps!

carloscmferreira0704

Copper Contributor

Jun 05, 2023

Dear NikolinoDE, thanks for your comments and inspiration! I researched a little bit more about these types of filters and achieved a useful code that I can now. This code is running perfectly to my objectives.

Sub MostrarTodasLinhas()
    'Macro Name
    
    Dim tbl As ListObject
        'Declares a variable called "tbl" of type "ListObject" that represents a table in Excel.
    Set tbl = ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO")
        'Assigns the variable "tbl" the reference to the specific table in Excel that is located in the worksheet "SIIPP" and has the name "TableGO"..
    If tbl.AutoFilter.FilterMode Then
        tbl.AutoFilter.ShowAllData
    End If
        'The macro checks whether filter mode is activated for the table "TableGO" using the FilterMode property of the AutoFilter function.
        'If enabled, the ShowAllData function is called to show all rows of the table again.
End Sub

Sub Filtro() 'The comments used in this macro are also valid for subsequent ones..
    'Macro Name
    Dim numeroColuna As Variant
        'Declares a variable called "numeroColuna" of type "Variant" that is a special type in VBA that can store different types of data.
    numeroColuna = ThisWorkbook.Worksheets("SIIPP").Range("O5").Value
        'Assigns to the variable "numeroColuna" the value of the specific cell "O5" spreadsheet "SIIPP" that contains the number of the column that will be filtered.
    Dim critério As String
        'Declares a variable called "critério" of type "String" that is a sequence of characters.
    critério = ThisWorkbook.Worksheets("SIIPP").Range("O4").Value
        'Assigns to the variable "critério" the value of the specific cell "O4" worksheet "SIIPP" that contains the filter criterion.
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        'This defines the scope of the With block. It indicates that all subsequent operations will apply to the range of cells in the "TableGO" table
        'in the "SIIPP" sheet of the current workbook.
        .AutoFilter Field:=numeroColuna, Criteria1:=critério, Operator:=xlFilterValues
        'AutoFilter is used to apply a filter to the table.
        'Field:=numberColumn specifies the field (column) by which the filter will be applied.
        'Criteria1:=critério specifies the filter criteria to be applied.
        'Operator:=xlFilterValues specifies the filter operator to be used. In this case, xlFilterValues indicates that the filtered values should
        'exactly match the specified criterion.
    End With
End Sub

Sub FiltroDE()
    Dim numeroColunaDE As Variant
    numeroColunaDE = ThisWorkbook.Worksheets("SIIPP").Range("P5").Value    
    Dim critérioDE As String
    critérioDE = ThisWorkbook.Worksheets("SIIPP").Range("P4").Value    
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        .AutoFilter Field:=numeroColunaDE, Criteria1:=critérioDE, Operator:=xlFilterValues
    End With
End Sub

Sub FiltroAP()
    Dim numeroColunaAP As Variant
    numeroColunaAP = ThisWorkbook.Worksheets("SIIPP").Range("Q5").Value    
    Dim critérioAP As String
    critérioAP = ThisWorkbook.Worksheets("SIIPP").Range("Q4").Value    
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        .AutoFilter Field:=numeroColunaAP, Criteria1:=critérioAP, Operator:=xlFilterValues
    End With
End Sub

Sub FiltroODS()
    Dim numeroColunaODS As Variant
    numeroColunaODS = ThisWorkbook.Worksheets("SIIPP").Range("R5").Value    
    Dim critérioODS As String
    critérioODS = ThisWorkbook.Worksheets("SIIPP").Range("R4").Value    
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        .AutoFilter Field:=numeroColunaODS, Criteria1:=critérioODS, Operator:=xlFilterValues
    End With
End Sub

Sub FiltroPEDS()
    Dim numeroColunaPEDS As Variant
    numeroColunaPEDS = ThisWorkbook.Worksheets("SIIPP").Range("S5").Value    
    Dim critérioPEDS As String
    critérioPEDS = ThisWorkbook.Worksheets("SIIPP").Range("S4").Value    
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        .AutoFilter Field:=numeroColunaPEDS, Criteria1:=critérioPEDS, Operator:=xlFilterValues
    End With
End Sub

Sub FiltroFONTE()
    Dim numeroColunaFONTE As Variant
    numeroColunaFONTE = ThisWorkbook.Worksheets("SIIPP").Range("T5").Value    
    Dim critérioFONTE As String
    critérioFONTE = ThisWorkbook.Worksheets("SIIPP").Range("T4").Value    
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        .AutoFilter Field:=numeroColunaFONTE, Criteria1:=critérioFONTE, Operator:=xlFilterValues
    End With
End Sub

Sub FiltroIP()
    Dim numeroColunaIP As Variant
    numeroColunaIP = ThisWorkbook.Worksheets("SIIPP").Range("U5").Value    
    Dim critérioIP As String
    critérioIP = ThisWorkbook.Worksheets("SIIPP").Range("U4").Value     
    With ThisWorkbook.Worksheets("SIIPP").ListObjects("TableGO").Range
        .AutoFilter Field:=numeroColunaIP, Criteria1:=critérioIP, Operator:=xlFilterValues
    End With
End Sub

share here:

Forum Discussion

How to filter results of previous filters using advanced filters

Resources