Illegal Field Name

[Joe]

I have a macro that is using the Advanced Filter with ranges names defined
inside the filter. I keep getting an error message "The extract range has a
missing or illegal field name" I can't figure out what it is really telling
me. All the field names are in the header row on both the list and copy to
ranges.

 

[Jan Karel Pieterse]

Hi Joe,

All the field names are in the header row on both the list and copy to
ranges.

Are the field names unique?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Joe]

The range names are the same in both. There is a "blank" column, no name, in
the list range with no blank column in the extract range. I wonder if this
is it? But, the real question is exactly what does that error message mean.

Joe

 

[Jan Karel Pieterse]

Hi Joe,

But, the real question is exactly what does that error message mean.

The advanced filter can be really picky about the correct use of FIELD names
(the heading names of your tables). The field names you used in the criteria
range must either match those in the table exactly, or -in certain conditions,
e.g. when using formula's for criteria- they must be absent from the criteria
range altogether. I suspect that is what causes your error message.

Try if your advanced filter setup works manually.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Shane Devenshire]

Hi,

The safest way to make sure the spellings of all field names are identical
in the List Range, Criteria Range and Output Range is the 1. Copy and paste
them, or 2. reference them with formulas. The most common problem is an
extra space within or at the end of the field name, for example First Name <>
First Name.

As mentioned if you use a calculated criteria, its field name must not match
any of those in the List Range. However, I suspect this is not the issue.

Another issue if the number of fields in the output range are changing you
need to modify the code to handle that. In other words if the output range
is four columns and then you clear one of the titles, listing only three
titles and rerun the advanced filter command, you need to make sure you
reselect the new output range titles. A blank field name in the output range
will generate your error message.