PDA

View Full Version : Filtering Column with Multiple Criteria



dnraitzyk
09-13-2017, 07:13 PM
Hello all,

I am new here and I am not exactly a coder. I am trying to make a simple macro that will sort a workbook that I download almost daily and sort the data by the criteria in each column. I cannot post the actual data here for privacy reasons. Basically, I would like to sort a column that has >50 values to 16 specific values all of which are very long phrases of words. An example would be like sorting this column:

Header
aaaaaaaaaaaa aaaaaaaaaaaaa aaaaaaaaaaaaa
bbbbbbbbbbb bbbbbbbbbb bbbbbbbbbbbb
cccccccccc cccccccccccccccc cccccccccccccc
dddddddddd dddddddddddddd ddddddddddddd
eeeeeeeeeeeeee eeeeeeeeeee eeeeeeeeeeeeee

Obviously there are more rows in my actual data, but let's say in this example I would just want to show the "c" and "e" row. I tried using autofilter commands like so. Keep in mind I am not a coder and all of this I have adapted from online. I would like the simplest solution possible.

Last2 = Cells(Rows.Count, 7).End(xlUp).Row
Selection.AutoFilter
Range("G5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("$G$5:G" & Last2).AutoFilter Field:=1, Criteria1:=Array("aaaaaaaaaaaaaaaaaa aaaaaaaaaaaaa aaaaaaaaa" _
, "bbbbbbbbbbb bbbbbbbbbbbb bbbbbbbbbbbbbb" _
, "cccccccccccc cccccccc ccccccccccccccccccccccc" _
, "dddddddddddddddddddd dddddddddddddddd ddddddddddddddddd" _
, "eeeeeeeeeeeeeeeeeeee eeeeeeeeeeeeeeeeeeee eeeeeeeeeeeeeeeeeeeeeeee"), Operator:=xlFilterValues

What am I doing wrong? Any help would be greatly appreciated.

dnraitzyk
09-13-2017, 07:57 PM
To add on to this, I am able to filter columns that only have 1 or 2 criteria, but the second filter will undo the first. How can I get them all to work together?

mdmackillop
09-14-2017, 02:14 AM
Can you mock up a file and post it to save responders each making their own?

Paul_Hossler
09-14-2017, 07:13 AM
This is just the filtering part, if you want help on a sort macro then as Mac says a representative workbook would be helpful


I'd suggest AdvancedFilter which uses a CriteriaRange. Might not even need a macro

Set up a Criteria range (green), where the D2 and D3 are the values (i.e. 'OR'). BTW more column headers in E:E or more with values below equate to an AND

20342


Manually apply AdvancedFilter from the Data tab


20341






This is simple recorded macro



Sub Macro1()
Range("A1").Select
Range("A1:A6").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("D1:D3"), Unique:=False
' ActiveSheet.ShowAllData
' Selection.AutoFilter
End Sub

dnraitzyk
09-14-2017, 09:32 AM
2034720347

So lets assume I wanted to sort by place1, and then also stuff2, and then the "b", "c", and "d" rows.

mdmackillop
09-14-2017, 10:45 AM
This contains a UserForm tool I made up for such filtering. Let me know if there are any issues
BTW, Sort and Filter are different procedures.