I have tried to add the below VBA-code from
http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new module, but
it continues to return #NAME? when I try to use the function:

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If

End With
End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function

Does this indicate an error in the VBA syntax or perhaps that the module
is not made available?

Mogens

Where are you putting the code? It should be in a regular code
module, NOT the ThisWorkbook code module or a sheet module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"mogens" wrote in message
...
I have tried to add the below VBA-code from
http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new
module, but it continues to return #NAME? when I try to use the
function:

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If

End With
End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 &
strCri2
End Function

Does this indicate an error in the VBA syntax or perhaps that
the module is not made available?

Mogens

How do you call the function (what is your formula)?
Did you try inserting it via the Function wizard?

--
Kind regards,

Niek Otten

"Chip Pearson" wrote in message ...
Where are you putting the code? It should be in a regular code module, NOT the ThisWorkbook code module or a sheet module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"mogens" wrote in message ...
I have tried to add the below VBA-code from http://www.ozgrid.com/VBA/autofilter-criteria.htm in a new module, but it continues
to return #NAME? when I try to use the function:

Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String

Application.Volatile

With Header.Parent.AutoFilter
With .Filters(Header.Column - .Range.Column + 1)

If Not .On Then Exit Function

strCri1 = .Criteria1
If .Operator = xlAnd Then
strCri2 = " AND " & .Criteria2
ElseIf .Operator = xlOr Then
strCri2 = " OR " & .Criteria2
End If

End With
End With

AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function

Does this indicate an error in the VBA syntax or perhaps that the module is not made available?

Mogens

Chip Pearson wrote:
Where are you putting the code? It should be in a regular code
module, NOT the ThisWorkbook code module or a sheet module.


Thanks Chip - that explained the error.

I am trying to find a way to see how to qualify for instance how to
select only records with "blanks". This formula didn't give much of an
explanation though.

A shame that you are not able to switch between different criteria for
an autofilter as the advanced filter is a mystery to me (doesn's seem to
update automatically and I can't find somewhere with an easy overview of
criteria definitions) :-(

field:=1, Criteria1:="="

for blanks

field:=1, Criteria:="<"

for non-blanks.

--
Regards,
Tom Ogilvy




"mogens" wrote:

Chip Pearson wrote:
Where are you putting the code? It should be in a regular code
module, NOT the ThisWorkbook code module or a sheet module.


Thanks Chip - that explained the error.

I am trying to find a way to see how to qualify for instance how to
select only records with "blanks". This formula didn't give much of an
explanation though.

A shame that you are not able to switch between different criteria for
an autofilter as the advanced filter is a mystery to me (doesn's seem to
update automatically and I can't find somewhere with an easy overview of
criteria definitions) :-(