How to filter results of previous filters using advanced filters

Question

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, the second filter does not consider only the results provided by the first one.&nbsp;For each filter, I have built a different button, and there is also a macro the clears all the results (this should be kept).&nbsp;The code I am using is as follows:Sub LimpaFiltro()
' LimpaFiltro Macro
    ActiveSheet.ShowAllData
End Sub

Sub Filtro()
' Filtro Macro       
    With ThisWorkbook.Worksheets("SIIPP")
        .ListObjects("TableGO").Range.AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("O3:O4"), _
            Unique:=False
    End With
End Sub

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

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

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

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

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

Sub FiltroIP()
' Filtro Macro    
    With ThisWorkbook.Worksheets("SIIPP")
        .ListObjects("TableGO").Range.AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=.Range("U3:U4"), _
            Unique:=False
    End With
End Sub&nbsp;Can you help me adapt this code such that when I apply a first filter and then a second one, the second filter considers only the results already provided by the first filter?&nbsp;Thanks in advance for your help!

nikolinode · Answer

carloscmferreira0704&nbsp;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 SubIn 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 · Answer

NikolinoDE&nbsp;&nbsp;Thank you so much for your help! However, I got the following error:&nbsp;&nbsp;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 · Answer

carloscmferreira0704&nbsp;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:Ensure that the table name ("TableGO") is correct and matches the name of your table in the worksheet "SIIPP".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.Verify that the table range being filtered ("TableGO[#All]") covers the entire table range, including headers and data.Make sure there are no merged cells within the table range or the criteria range, as this can cause issues with the AdvancedFilter method.Check if the worksheet "SIIPP" is protected or locked. If it is, temporarily remove the protection to see if it resolves the error.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 · Answer

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--&gt;IB. From columns B--&gt;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 range5. 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 · Answer

carloscmferreira0704&nbsp;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 SubAfter running these modified macros in sequence (e.g.,&nbsp;Filtro, then&nbsp;FiltroDE, then&nbsp;FiltroAP, etc.), each subsequent filter will be applied to the results of the previous filter.I hope this helps!

Forum Discussion

How to filter results of previous filters using advanced filters

7 Replies

Resources