Advanced Filter

[msohail]

Hi,
I am trying to use the advanced filter tool to paste 'uniqe records only'. I do not however want to include the column headings in the 'list range'. If I don't include these headings in the range, when I 'paste to another location'I get an error message "the extract range has a missing or illegal filed name". Is there any way around this pls??

 

[Andrew Poulsom]

It should work if you sort the original range, then select from the last occurrence of item 1 to the end of the list. Excel will think item 1 is the header.

 

[msohail]

I've sorted the range and then selected from last record to first. Same result, got the same message about not having column labels!! Pls continue to help.

 

[Andrew Poulsom]

That's a different error and it's noly a warning (not fatal).

Say you have a list like this:

A,1,2,1,2,1,2

where A is the header. Sort the list by A and you get:

A,1,1,1,2,2,2

Select the last 4 items and choose Dat, Filter, Advanced Filter. Excel displays a message box saying "Microsoft Excel cannot determine which row in your list contains column labels BLAH BLAH". Click OK to use the first row as labels then continue as normal. The Extract range will contain:

1,2

 

[msohail]

thanks,
I'm working in Excel 97 - when I've followed your method I do no get the nessage "Microsoft Excel cannot determine which row in your list contains column labels..". As I'm trying to record the routine into a MACRO I can't even simply let the selection include the column lable and delete that row afterwards. Any other suggestions - pls fwd.

 

[Andrew Poulsom]

I get the same message in Excel 97. If I record my actions this is what I get:

Range("A4:A7").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C4"), Unique:=True

If I run the macro I get no errors.

 

[msohail]

When u say u get no errors, is that to say the macro performs as intended?? I have copied your VB text into my Macro and adjusted the ranges as per s/sheet but to no avail. The dreaded msg of "the extract range has missing or ilegal field name" still appears. Is there any other method apart from using 'advanced filter' to return unique records??

 

[Andrew Poulsom]

You only get that message if the list includes the heading more than once. My solution tricks Excel into thinking that 1 is the heading by selecting from the last 1 to the bottom of the (sorted) list. So it copies the heading and a unique 2.

 

[msohail]

Bcoz i'm using it in a macro the last'1' will always change. Thanks,
I'm looking for the nacro to work every time regardless of changes in sort etc

 

[Andrew Poulsom]

Post your code and I'll have a look at it.