Forum Discussion
How to filter results of previous filters using advanced filters
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.
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!
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.
- 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!
