Filter auslesen
27.07.2022 08:54:08
Günther
ich habe nachfolgende Funktion im Internet zum auslesen des Filters gefunden. Leider erhalte ich kein Ergebnis. Was mache ich falsch?
In Zelle A1 habe ich
=showfilter(A4:A14)&ZEICHEN(TEILERGEBNIS(9;A2)*0+32) eingetragen
A4 B4 C4
Name Vorname Ort
Bäcker Hans Paris
Müller Josef München
Metzger Heinz Oslo
Elektriker Dietmar Madrid
Müller Jürgen ulm
Jaques Michelle Paris
Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sOp As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Dim i As Long
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range
If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
lngOp = filt.Operator
If lngOp = xlFilterValues Then
For i = LBound(filt.Criteria1) To UBound(filt.Criteria1)
sCrit1 = sCrit1 & filt.Criteria1(i) & " or "
Next i
sCrit1 = Left(sCrit1, Len(sCrit1) - 3)
Else
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
If lngOp = xlAnd Then
sOp = " And "
ElseIf lngOp = xlOr Then
sOp = " or "
Else
sOp = ""
End If
End If
ShowFilter = sCrit1 & sOp & sCrit2
End If
End If
End Function