Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Excel 2010, VBA, Autofilter abfragen bei .Operator=xlFilterValues
528 views
Skip to first unread message
Bernhard Sander
Jul 17, 2014, 9:23:16 AM7/17/14
to
Hallo,
wie kann ich mit VBA die Autofilter-Einstellungen abfragen, wenn in der
gefilterten Spalte Datumseintrï¿œge stehen und einige (Jahres-)Werte im Treeview
ausgewï¿œhlt sind.
VBA wirft bei Abfrage von Filters.item(x).Criteria1 oder .Criteria2 einen
Fehler. Hab noch nichts mit Hilfe von Google oder gar in der MS-Doku dazu
gefunden (ausser dass es ziemlich versteckt sein soll...).
Ich wï¿œrde gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
was erledigen was den Autofilter nicht vertrï¿œgt und danach den Autofilter wieder
komplett restaurieren.
Gruￜ
Bernhard Sander
wie kann ich mit VBA die Autofilter-Einstellungen abfragen, wenn in der
gefilterten Spalte Datumseintrï¿œge stehen und einige (Jahres-)Werte im Treeview
ausgewï¿œhlt sind.
VBA wirft bei Abfrage von Filters.item(x).Criteria1 oder .Criteria2 einen
Fehler. Hab noch nichts mit Hilfe von Google oder gar in der MS-Doku dazu
gefunden (ausser dass es ziemlich versteckt sein soll...).
Ich wï¿œrde gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
was erledigen was den Autofilter nicht vertrï¿œgt und danach den Autofilter wieder
komplett restaurieren.
Gruￜ
Bernhard Sander
Claus Busch
Jul 17, 2014, 9:52:37 AM7/17/14
to
Hallo Bernhard,
Am Thu, 17 Jul 2014 15:23:16 +0200 schrieb Bernhard Sander:
> Ich w锟絩de gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
> was erledigen was den Autofilter nicht vertr锟絞t und danach den Autofilter wieder
> komplett restaurieren.
angepasst aus der Hilfe. Wenn du mehrere Spalten gefiltert hast,
m锟絪stest du zur Abfrage noch eine Schleife 锟絙er f laufen lassen.
Der Operator wird mit Integers zur锟絚k gegeben: 1 ist "UND", 2 ist "ODER"
Sub ChangeFilters()
Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String
Dim f As Long, n As Long
Dim myStr As String
Set w = Worksheets("Tabelle1")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(0 To .Count - 1, 0 To 2)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f - 1, 0) = .Criteria1
If .Operator Then
filterArray(f - 1, 1) = .Operator
filterArray(f - 1, 2) = .Criteria2
End If
End If
End With
Next
End With
End With
For n = 0 To 2
myStr = myStr & filterArray(f - 2, n) & Chr(10)
Next
MsgBox myStr
End Sub
Mit freundlichen Gr锟斤拷en
Claus
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Am Thu, 17 Jul 2014 15:23:16 +0200 schrieb Bernhard Sander:
> Ich w锟絩de gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
> was erledigen was den Autofilter nicht vertr锟絞t und danach den Autofilter wieder
> komplett restaurieren.
angepasst aus der Hilfe. Wenn du mehrere Spalten gefiltert hast,
m锟絪stest du zur Abfrage noch eine Schleife 锟絙er f laufen lassen.
Der Operator wird mit Integers zur锟絚k gegeben: 1 ist "UND", 2 ist "ODER"
Sub ChangeFilters()
Dim w As Worksheet
Dim filterArray()
Dim currentFiltRange As String
Dim f As Long, n As Long
Dim myStr As String
Set w = Worksheets("Tabelle1")
With w.AutoFilter
currentFiltRange = .Range.Address
With .Filters
ReDim filterArray(0 To .Count - 1, 0 To 2)
For f = 1 To .Count
With .Item(f)
If .On Then
filterArray(f - 1, 0) = .Criteria1
If .Operator Then
filterArray(f - 1, 1) = .Operator
filterArray(f - 1, 2) = .Criteria2
End If
End If
End With
Next
End With
End With
For n = 0 To 2
myStr = myStr & filterArray(f - 2, n) & Chr(10)
Next
MsgBox myStr
End Sub
Mit freundlichen Gr锟斤拷en
Claus
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Claus Busch
Jul 17, 2014, 9:54:40 AM7/17/14
to
Hallo Bernhard,
Am Thu, 17 Jul 2014 15:52:37 +0200 schrieb Claus Busch:
> For n = 0 To 2
> myStr = myStr & filterArray(f - 2, n) & Chr(10)
> Next
> MsgBox myStr
f-1 ist die gefilterte Spalte
Mit freundlichen Gr��en
Am Thu, 17 Jul 2014 15:52:37 +0200 schrieb Claus Busch:
> For n = 0 To 2
> myStr = myStr & filterArray(f - 2, n) & Chr(10)
> Next
> MsgBox myStr
Mit freundlichen Gr��en
Claus Busch
Jul 17, 2014, 10:51:00 AM7/17/14
to
Hallo Bernhard,
Am Thu, 17 Jul 2014 15:23:16 +0200 schrieb Bernhard Sander:
> Ich w�rde gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
> was erledigen was den Autofilter nicht vertr�gt und danach den Autofilter wieder
> komplett restaurieren.
ich habe noch eine nette Funktion gefunden, die dir die Kriterien im
Blatt ausgibt.
Du kannst sie aufrufen mit =ShowFilter(A:A) und nach rechts ziehen so
weit ben�tigt:
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
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
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
Am Thu, 17 Jul 2014 15:23:16 +0200 schrieb Bernhard Sander:
> was erledigen was den Autofilter nicht vertr�gt und danach den Autofilter wieder
> komplett restaurieren.
ich habe noch eine nette Funktion gefunden, die dir die Kriterien im
Blatt ausgibt.
Du kannst sie aufrufen mit =ShowFilter(A:A) und nach rechts ziehen so
weit ben�tigt:
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
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
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
Bernhard Sander
Jul 18, 2014, 7:06:58 AM7/18/14
to
Hallo Claus,
vielen Dank f锟絩 Deine Antwort.
>> Ich w锟絩de gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
>> was erledigen was den Autofilter nicht vertr锟絞t und danach den Autofilter wieder
>> komplett restaurieren.
>
> angepasst aus der Hilfe. Wenn du mehrere Spalten gefiltert hast,
> m锟絪stest du zur Abfrage noch eine Schleife 锟絙er f laufen lassen.
Das Beispiel aus der Hilfe hatte ich auch schon adaptiert.
Problem:
Schreibe in eine Spalte Dat锟絤er, m锟絞lichst 锟絙er mehrere Jahre (muss ja nicht f锟絩
jeden Tag eine Zeile sein ;-) ) z.B. so:
Datum
01.01.2011
02.02.2012
03.03.2013
Schalte den Autofilter ein.
Nun kann man in der Kopfzeile ein Men锟�aufklappen. Darin wird ein Treeview mit
den vorhandenen Eintr锟絞en der Spalte angezeigt. In der ersten Ebene stehen die
Jahre, klappt man diese auf, erscheinen die Monate, die kann man wiederum
aufklappen und es erscheinen die Tage.
Setzt man nun in diesem Treeview irgendwelche H锟絢chen, dann steigt das Beispiel
aus der Hilfe an dieser Stelle:
Im 锟絙erwachungsfenster erkennt man, dass .Criteria1 und auch .Criteria2 nicht
verf锟絞bar sind. .Operator hat dabei den Wert xlFilterValues.
Excel speichert die im Treeview gew锟絟lten Werte irgendwo anders ab, leider hab
ich noch keine Beschreibung dazu gefunden.
> Sub ChangeFilters()
> Dim w As Worksheet
> Dim filterArray()
> Dim currentFiltRange As String
> Dim f As Long, n As Long
> Dim myStr As String
>
> Set w = Worksheets("Tabelle1")
> With w.AutoFilter
> currentFiltRange = .Range.Address
> With .Filters
> ReDim filterArray(0 To .Count - 1, 0 To 2)
> For f = 1 To .Count
> With .Item(f)
> If .On Then
> filterArray(f - 1, 0) = .Criteria1
> If .Operator Then
> filterArray(f - 1, 1) = .Operator
> filterArray(f - 1, 2) = .Criteria2
> End If
> End If
> End With
> Next
> End With
> End With
Viele Gr锟斤拷e
Bernhard Sander
vielen Dank f锟絩 Deine Antwort.
>> Ich w锟絩de gerne meine Autofilter-Einstellungen abfragen, den Filter ausschalten,
>> was erledigen was den Autofilter nicht vertr锟絞t und danach den Autofilter wieder
>> komplett restaurieren.
>
> angepasst aus der Hilfe. Wenn du mehrere Spalten gefiltert hast,
> m锟絪stest du zur Abfrage noch eine Schleife 锟絙er f laufen lassen.
Problem:
Schreibe in eine Spalte Dat锟絤er, m锟絞lichst 锟絙er mehrere Jahre (muss ja nicht f锟絩
jeden Tag eine Zeile sein ;-) ) z.B. so:
Datum
01.01.2011
02.02.2012
03.03.2013
Schalte den Autofilter ein.
Nun kann man in der Kopfzeile ein Men锟�aufklappen. Darin wird ein Treeview mit
den vorhandenen Eintr锟絞en der Spalte angezeigt. In der ersten Ebene stehen die
Jahre, klappt man diese auf, erscheinen die Monate, die kann man wiederum
aufklappen und es erscheinen die Tage.
Setzt man nun in diesem Treeview irgendwelche H锟絢chen, dann steigt das Beispiel
aus der Hilfe an dieser Stelle:
filterArray(f - 1, 0) = .Criteria1
mit einer Fehlermeldung aus.
Im 锟絙erwachungsfenster erkennt man, dass .Criteria1 und auch .Criteria2 nicht
verf锟絞bar sind. .Operator hat dabei den Wert xlFilterValues.
Excel speichert die im Treeview gew锟絟lten Werte irgendwo anders ab, leider hab
ich noch keine Beschreibung dazu gefunden.
> Sub ChangeFilters()
> Dim w As Worksheet
> Dim filterArray()
> Dim currentFiltRange As String
> Dim f As Long, n As Long
> Dim myStr As String
>
> Set w = Worksheets("Tabelle1")
> With w.AutoFilter
> currentFiltRange = .Range.Address
> With .Filters
> ReDim filterArray(0 To .Count - 1, 0 To 2)
> For f = 1 To .Count
> With .Item(f)
> If .On Then
> filterArray(f - 1, 0) = .Criteria1
> If .Operator Then
> filterArray(f - 1, 1) = .Operator
> filterArray(f - 1, 2) = .Criteria2
> End If
> End If
> End With
> Next
> End With
> End With
Bernhard Sander
Claus Busch
Jul 18, 2014, 7:55:21 AM7/18/14
to
Hallo Bernhard,
Am Fri, 18 Jul 2014 13:06:58 +0200 schrieb Bernhard Sander:
> filterArray(f - 1, 0) = .Criteria1
> mit einer Fehlermeldung aus.
> Im �berwachungsfenster erkennt man, dass .Criteria1 und auch .Criteria2 nicht
> verf�gbar sind.
bei mehr als 2 Kritierien ist der Wert ein semikolon-getrennter String.
Du kannst mal mit der Maus �ber den DropDown-Pfeil des Filters gehen.
Wenn du z.B. die Zahlen 1 bis 10 in der Spalte hast und willst 1, 3, 5
und 7 anzeigen, wird dir gezeigt
Ist gleich "1; 3; 5; 7"
Wie das auszulesen ist, wei� ich aber auch nicht.
Eine M�glichkeit w�re, die sichtbaren Zellen in ein Array zu schreiben,
eindeutige Werte daraus zu erstellen und diese Werte in einem String
ausgeben.
Mit freundlichen Gr��en
Am Fri, 18 Jul 2014 13:06:58 +0200 schrieb Bernhard Sander:
> filterArray(f - 1, 0) = .Criteria1
> mit einer Fehlermeldung aus.
> verf�gbar sind.
bei mehr als 2 Kritierien ist der Wert ein semikolon-getrennter String.
Du kannst mal mit der Maus �ber den DropDown-Pfeil des Filters gehen.
Wenn du z.B. die Zahlen 1 bis 10 in der Spalte hast und willst 1, 3, 5
und 7 anzeigen, wird dir gezeigt
Ist gleich "1; 3; 5; 7"
Wie das auszulesen ist, wei� ich aber auch nicht.
Eine M�glichkeit w�re, die sichtbaren Zellen in ein Array zu schreiben,
eindeutige Werte daraus zu erstellen und diese Werte in einem String
ausgeben.
Mit freundlichen Gr��en
Claus Busch
Jul 18, 2014, 8:02:10 AM7/18/14
to
Hallo Bernhard
Am Fri, 18 Jul 2014 13:06:58 +0200 schrieb Bernhard Sander:
Probiere es dann mal so:
With Worksheets("Tabelle1")
For Each f In .AutoFilter.Filters
If f.On Then
c1 = Join(f.Criteria1, ",")
MsgBox c1
.
.
.
Mit freundlichen Gr��en
Am Fri, 18 Jul 2014 13:06:58 +0200 schrieb Bernhard Sander:
> filterArray(f - 1, 0) = .Criteria1
wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
Probiere es dann mal so:
With Worksheets("Tabelle1")
For Each f In .AutoFilter.Filters
If f.On Then
c1 = Join(f.Criteria1, ",")
MsgBox c1
.
.
.
Mit freundlichen Gr��en
Claus Busch
Jul 18, 2014, 8:12:51 AM7/18/14
to
Hallo Bernhard,
Am Fri, 18 Jul 2014 14:02:10 +0200 schrieb Claus Busch:
> wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
> Probiere es dann mal so:
With Worksheets("Tabelle1")
For Each f In .AutoFilter.Filters
If f.On And f.Operator = xlFilterValues Then
c1 = Replace(Join(f.Criteria1, ","), "=", " ")
Am Fri, 18 Jul 2014 14:02:10 +0200 schrieb Claus Busch:
> wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
> Probiere es dann mal so:
With Worksheets("Tabelle1")
For Each f In .AutoFilter.Filters
c1 = Replace(Join(f.Criteria1, ","), "=", " ")
Claus Busch
Jul 18, 2014, 8:19:31 AM7/18/14
to
Hallo nochmals,
Am Fri, 18 Jul 2014 14:12:51 +0200 schrieb Claus Busch:
>> wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
>> Probiere es dann mal so:
oder du fragst das Filterkriterium mit IsArray ab:
With Worksheets("Tabelle1")
For Each f In .AutoFilter.Filters
If IsArray(f.Criteria1) Then
Am Fri, 18 Jul 2014 14:12:51 +0200 schrieb Claus Busch:
>> wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
>> Probiere es dann mal so:
With Worksheets("Tabelle1")
For Each f In .AutoFilter.Filters
c1 = Replace(Join(f.Criteria1, ","), "=", " ")
MsgBox c1
MsgBox c1
Bernhard Sander
Jul 18, 2014, 8:34:23 AM7/18/14
to
Hallo Claus,
das Verr�ckte ist, wenn die Spalte "normale" Werte, also Zahlen oder Texte,
enth�lt, dann werden die im Autofilter angehakten Werte als Array in .Criteria1
abgelegt. Der Treeview im Autofilter hat nur eine Ebene.
Wenn die Spalte jedoch Datumswerte enth�lt, dann speichert Excel die Werte nicht
in .Criteria1 sondern irgendwo ganz anders. Im Treeview gibt es dann auch 3
Ebenen, die die Werte nach Datumsanteilen (Jahr, Monat, Tag) gruppieren und in
Zweigen anbietet.
>> filterArray(f - 1, 0) = .Criteria1
>
> wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
> Probiere es dann mal so:
>
> With Worksheets("Tabelle1")
> For Each f In .AutoFilter.Filters
> If f.On Then
> c1 = Join(f.Criteria1, ",")
> MsgBox c1
Viele Gr��e
Bernhard Sander
das Verr�ckte ist, wenn die Spalte "normale" Werte, also Zahlen oder Texte,
enth�lt, dann werden die im Autofilter angehakten Werte als Array in .Criteria1
abgelegt. Der Treeview im Autofilter hat nur eine Ebene.
Wenn die Spalte jedoch Datumswerte enth�lt, dann speichert Excel die Werte nicht
in .Criteria1 sondern irgendwo ganz anders. Im Treeview gibt es dann auch 3
Ebenen, die die Werte nach Datumsanteilen (Jahr, Monat, Tag) gruppieren und in
Zweigen anbietet.
>> filterArray(f - 1, 0) = .Criteria1
>
> wenn du nach mehreren Werten filterst, ist Criteria1 ein Array.
> Probiere es dann mal so:
>
> With Worksheets("Tabelle1")
> For Each f In .AutoFilter.Filters
> If f.On Then
> c1 = Join(f.Criteria1, ",")
> MsgBox c1
Bernhard Sander
0 new messages