Solved: Filtering issue

[dimitris_c]

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?

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

[dimitris_c]

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

Thanks

[mdmackillop]

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!A164").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("FilterCrit"), _
CopyToRange:=Range("Output!A1"), Unique:=False

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

[dimitris_c]

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