VBA for Excel 2007 using an Advanced Filter

[FuzzyTom]

Hi,

I have a range say A1:A100 that is populated with Client Names.

I would like to use Advance Filter to copy unique values of Client Names to another Range ... say G1 and in another case H1.

This is the criteria I have been testing for a while with no success:

in Range B2, my criteria range, I have:

="=*-ccs"

My thinking being that I would like all unique entries in A1:A100 that have "-ccs" as part of the Client name copied to G1

Then for my second case my criteria range, C2, is:

="<>*.ccs"

Again, I was hoping for all Client Names that do not have "-ccs" as part of the Client Name copied to H1.

I was thinking when I get this to work I would record the whole operation and thereby see the VBA code necessary to achieve the result.

Well, I cannot even get this to work ... typically that means I need to get more knowledge and being smart I know exactly where to go for this knowledge.

Any pointers, suggestions or help you can offer will be appreciated.

Thanks.
Tom.

 

[Momentman]

Here are the criteria i used for the advanced filter

Excel 2010
	D	E	F	G	H
1	Name			Name	Name
2	jane-ccs is not good			*-ccs*	<>*-ccs*
3	kahjdsadskadsljsdalksda
4	adsjkadsksadkjsad -ccs			Name	Name
5	dsahssdajkdsjkdsjak			jane-ccs is not good	kahjdsadskadsljsdalksda
6	dsajdsjkadssdjsdasak			adsjkadsksadkjsad -ccs	dsahssdajkdsjkdsjak
7	-ccs is ntotootot			-ccs is ntotootot	dsajdsjkadssdjsdasak
8	this is not incredibke				this is not incredibke
Sheet5

 

[FuzzyTom]

Wow ... that was quick.

Thanks @Momentman ... do I setup my criteria range as you have it in Column G and H?

Tom

 

[Momentman]

Yes, the criteria ranges are G1-G2 for those that contain "-ccs" and H1:H2 for those that dont contain "-ccs"

The results are what you see from cell G4 in column G and H4 in column H

 

[FuzzyTom]

Works like a breeze .... Thanks.

 

[Momentman]

Works like a breeze .... Thanks.

You are welcome, glad to help