Advanced Filter doesn't work - manually and through macro

[justme101]

Hi folks,

So, i have a data set in one sheet (say rawdata) and i have another sheet which has the filter criteria in one column "K" (sheet name is rules). When i did an advanced filter, to get the values from this colum to filter the data set, nothing happened.

I get the following code when i recorded this activity:

Columns("T:T").AdvancedFilter Action:=x1FilterInPlace, CriteriaRange:= Sheets("Rules").Columns("K:K"), Unique:= False

What i need is to filter the data set using all values in this column K of Rules sheet, any value not in the set can be ignored, and then cut visible data to a new sheet. This is necessary as there can be addition and deletion to this column in time.

Please note that i need this to be done through a macro as this is a part of a big activity which is being automated through macro coding.

Thank you.

 

[RoryA]

You did not get that code from the macro recorder because x1FilterInPlace should be xlFilterInPlace - the second character should be a lower case L not the number 1.

If you filter using an entire column, you won't get any results since a blank criteria cell will return all data - I'm guessing you don't actually have criteria values in every cell of the column. You need to restrict the criteria range to only the relevant rows.

 

[justme101]

You did not get that code from the macro recorder because x1FilterInPlace should be xlFilterInPlace - the second character should be a lower case L not the number 1.

If you filter using an entire column, you won't get any results since a blank criteria cell will return all data - I'm guessing you don't actually have criteria values in every cell of the column. You need to restrict the criteria range to only the relevant rows.

Hey Rory, thank you for pointing that out. I actually typed this thing from my home PC as the code is on my work PC and i can't open this forum from there.

Now, coming to the blank part, yes, i was doing it wrongly as i selected the entire column for the criteria whereas the values were in only the first 4 cells, but the thing is that this list my grow upto 6 cells or reduce to 3 cells in the future.

So, let's say my data is like this:

FilterCriteria	1	2
23123	A	F
23454	FA	FASD
4656	EH	FD
657	A	HK
9999	AHB	FA
9999	AFD	AD
443412	ASA	VHHY
9999	YDG	TYD
22	SD	DFGS

<tbody>
</tbody>

Now, in another sheet there is this column where i have the list through which i want to filter:

FilterList

9999

657

120

<tbody>
</tbody>

In this case, the macro should filter out the data with these 3 criteria and if there is no criteria match or only some data match,then filter only with those data then move on with the next step (which i will write later on after this macro is made). As i said, this list will change on a weekly basis but for the life of me i cannot find anything on the web to work with this. It would be immensely appreciative if you could help me out with the code for this.

Thank you, in anticipation.

 

[justme101]

*BUMP* PLEASE help guys! really need to solve this!

 

[Fluff]

Try

CriteriaRange:= Sheets("Rules").Range("K2", Sheets("Rules").Range("K" & Rows.Count).End(xlUp))

 

[justme101]

Hi Fluff,

Thank you for the reply, but this does not work and i know why, it is related to the entire process of how an AdvancedFilter works. So, in my given scenario, this will not work. I got another solution but that is only partially working. So, this thread can be closed and i'll message you the link to the new thread and would really appreciate your help with that

 

[mikerickson]

The headers need to match for Advanced Filter to work. should "FilterList" be "FilterCriteria" or vice versa?

 

[MARK858]

On top of what Mike has said about getting your headers the same, you also need the header row included in the criteriarange so if your header is in K1 of your sheet rules change the K2 in the code Fluff posted to K1 so you end up with...

  Sheets("rawdata").Range("T1", Sheets("rawdata").Range("T" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterInPlace, _
  CriteriaRange:=Sheets("Rules").Range("K1", Sheets("Rules").Range("K" & Rows.Count).End(xlUp))

 

[Marcelo Branco]

It seems this thread is similar to another one
https://www.mrexcel.com/forum/excel-questions/1036533-filter-data-using-list-another-sheet.html

M.

 

[MARK858]

It seems this thread is similar to another one
https://www.mrexcel.com/forum/excel-questions/1036533-filter-data-using-list-another-sheet.html

M.

It is, for some reason the OP thought that there is something in how AdvancedFilter works that wouldn't work in this situation and because of that started a new thread to try a different method.
I don't know what the OP thinks the issue with AdvancedFilter is as they haven't stated it

but this does not work and i know why, it is related to the entire process of how an AdvancedFilter works. So, in my given scenario, this will not work