Advanced Filter

msohail

Board Regular
Joined
Oct 9, 2002
Messages
120
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??
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 9, 2002
Messages
120
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 9, 2002
Messages
120

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 9, 2002
Messages
120

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Oct 9, 2002
Messages
120
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top