Advanced Filter

[Bob@Sun]

Hi guys,

I need your help with selecting only the visible cells when defining the criteria range of an advanced filter.
In the attached file I have some data in sheet2. I need first to filter this range to show Criteria 1 only and then use the visible cells as a range for the advanced filter in sheet1.
For the advanced filter in sheet1: List range - A1:A11, Criteria Range - Visible cells in sheet2.

As a result visible cells in sheet1 must be Name1, Name2, Name8, and Name10.

Thanks for the help!

[JBeaucaire]

What is Criteria 1?

[JBeaucaire]

Also, the Advanced Filter may not be necessary unless there are duplicates in column A, which i don't see in your sample. If you're filtering the data prior to get a sub listing, are you just wanting to copy all the visible cells in column A to sheet2? If so, a regular copy command will do that because it usually only copies visible cells when an AutoFilter is active.

[Bob@Sun]

No, I do not need to copy. I need to filter. It is hard to explain why I need it, but that is what I need.

Filter Info in sheet1 based on the criteria in sheet2, Criteria 1 is just for testing purposes to show you that the range in sheet2 has some hidden cells between.I hope that is clear enough.

[Bob@Sun]

Any ideas?

[Andy Pope]

Assumes criteria1 on sheet2 has already been applied.

Uses column I on sheet1 to store advance filter criteria

Please Login or Register  to view this content.

[Bob@Sun]

Hi Andy,

This code is working great for me.

One question though. If I have one name list in the range A1:A11 and the same list starting again from A15 true A25 is it possible to filter both ranges at the same time?

I tried with this but is not filtering both ranges at the same time.

Any ideas?

Please Login or Register  to view this content.

[Andy Pope]

You can not filter 2 areas on a sheet.

You can extend the range covered by the filter

Please Login or Register  to view this content.

But this may not give you the result you want.

[Bob@Sun]

Ok, thanks Andy

Appriciate your help!