PDA

View Full Version : Solved: Filtering issue



dimitris_c
07-22-2008, 07:13 AM
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
07-22-2008, 07:15 AM
please note that I would like to do all this using VBA, and no excel functions.

Thanks

mdmackillop
07-22-2008, 10:50 AM
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.


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

dimitris_c
07-24-2008, 12:27 AM
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