Solved: Filtering issue

Hello all,

I was hoping you could help with this.. it has two parts:

1) I am trying to use the AdvancedFilter method to filter a list I receive every day (like the one in the "Input" sheet), based on a user-defined list (like the one in the "summary" sheet). I can't get Advanced filter to work.. here is the code (also included in the spreadsheet), can you advise if there is something wrong with this?

Code:

---

Sub FilterList()
 
Sheets("Output").Cells.Clear
 
Range("Input!A1:D64").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("Summary!A2:A13"), _
CopyToRange:=Range("Output!A1"), Unique:=False
 
Sheets("Output").Select
 
 
End Sub

---

2) As you can see in the "Input" sheet, the input also has a "flag" column. Basically I want to filter my output a second time, so that only the records with a 1 next to them are kept.

Do you think you can help?

Thanks a lot in advance,
Dimitris

please note that I would like to do all this using VBA, and no excel functions.

Thanks

Hi dimitris
Welcome to VBAX
The filter criteria needs the same headings as your data. You cannot have blank field in your criteria.
The attached solution uses a dynamic named range for the criteria, to avoid this problem. FilterList2 in the sample shows how the blank rows fail.

[VBA]
Sub FilterList()
Sheets("Output").Cells.Clear
Range("Input!A1:D64").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("FilterCrit"), _
CopyToRange:=Range("Output!A1"), Unique:=False

Sheets("Output").Select
End Sub
[/VBA]

Hello mdmackillop,

This works great! Thanks a lot! And also I had never thought of defining names for ranges using functions, that's nice to know.

Thanks again,
Dimitris