Forum Discussion

carloscmferreira0704's avatar
carloscmferreira0704
Copper Contributor
May 30, 2023

How to filter results of previous filters using advanced filters

Hi! I am trying to implement filters that work sequentially. Currently, I have seven filters that work independently, which that means if I apply one filter and then I try to apply another filter, th...
Macros and VBA
NikolinoDE's avatar
NikolinoDE
Gold Contributor
May 30, 2023

carloscmferreira0704 

To modify the code so that each subsequent filter considers the results provided by the previous filter, you can use the "xlFilterCopy" action instead of "xlFilterInPlace".

Here's an updated version of the code:

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

Sub Filtro()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("O3:O4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

Sub FiltroDE()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("P3:P4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

Sub FiltroAP()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("Q3:Q4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

Sub FiltroODS()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("R3:R4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

Sub FiltroPEDS()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("S3:S4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

Sub FiltroFonte()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("T3:T4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

Sub FiltroIP()
' Filtro Macro
With ThisWorkbook.Worksheets("SIIPP")
.ListObjects("TableGO").Range.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=.Range("U3:U4"), _
CopyToRange:=.Range("TableGO[#All]"), _
Unique:=False
End With
End Sub

In this updated code, each filter uses the "xlFilterCopy" action instead of "xlFilterInPlace". This action copies the filtered results to the entire table range ("TableGO[#All]").

By doing so, each subsequent filter will consider the results provided by the previous filter.

Please make sure to adjust the range references ("TableGO", "O3:O4", "P3:P4", etc.) to match your specific workbook and table structure.

Note: The "LimpaFiltro" macro for clearing all filters remains unchanged in this code.

carloscmferreira0704's avatar
carloscmferreira0704
Copper Contributor
May 30, 2023

NikolinoDE 

 

Thank you so much for your help! However, I got the following error:

 

 

Can you help me? I am not a specialist in VBA, so there are some features of the language that I do not know yet!

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    May 30, 2023

    carloscmferreira0704 

    The error "Run-time error 1004: AdvancedFilter method of Range class failed" usually occurs when there is an issue with the range references or the criteria range in the AdvancedFilter method. Here are a few things you can check to resolve the error:

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

    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.

    3. Verify that the table range being filtered ("TableGO[#All]") covers the entire table range, including headers and data.

    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.

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

    6. 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.

    By reviewing and addressing these potential issues, you should be able to resolve the "Run-time error 1004" and successfully apply the AdvancedFilter method with sequential filters.

    • carloscmferreira0704's avatar
      carloscmferreira0704
      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's avatar
        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!

Resources