1.2k Aufrufe
Gefragt in Tabellenkalkulation von ahorn38 Experte (3.2k Punkte)
Hallo,
ich bin bisher davon ausgegangen, daß ausgeblendete Zeilen beim Ausführen einer Schleife trotzdem mit "bearbeitet"/berücksichtigt werden. Jetzt stelle ich fest, daß ich unterschiedliche Ergebnisse in Abhängigkeit davon erhalte, ob bestimmte Zeilen innerhalb der Schleife ein- oder ausgeblendet sind. Kann mir jemand sagen, wie hier die grundsätzliche Regel lautet? Danke und Gruß.
A.

15 Antworten

0 Punkte
Beantwortet von
Hallo Andreas,

ich bin bisher davon ausgegangen, daß ausgeblendete Zeilen beim Ausführen einer Schleife trotzdem mit "bearbeitet"/berücksichtigt werden

Das ist korrekt. wenn du in VBA die Zeilen einzeln durchläufst ist es irrelevant, ob die Zeile ausgeblendet ist oder nicht. Wenn du ausgeblendete Zeilen ausschließen willst, solltest du über eine If-Bedingung die .Hidden-Eigenschaft der Zeile (Range-Objekt) abfragen.

Jetzt stelle ich fest, daß ich unterschiedliche Ergebnisse in Abhängigkeit davon erhalte, ob bestimmte Zeilen innerhalb der Schleife ein- oder ausgeblendet sind

Kann ich mir nicht vorstellen. Poste doch mal deine Schleife, dann kann man das Problem besser suchen.

Falls du allerdings das VBA-Ergebnis mit der Excel-Formel =Teilergebnis vergleichst wirst du eine Differenz feststellen. Ersetzte in diesem Fall die Formel Teilergebnis durch =Summe. Die schließt auch ausgeblendete Zeilen ein.

Gruß Mr. K.
0 Punkte
Beantwortet von ahorn38 Experte (3.2k Punkte)
Hallo,
danke erst einmal für die schnelle Antwort. ich bin auch erst einmal beruhigt, daß ich nicht die ganze Zeit schief gelegen habe. Um so verwunderlicher ist aber jetzt mein Problem (falls es dann überhaupt an dem Ausblenden liegt...?).
Die Datei ist sehr komplex und ich will nicht alles schicken, versuche aber einmal das Problem einzukreisen und den Auszug zu für ein upload vorzubereiten....
Bis später. Gruß A.
0 Punkte
Beantwortet von
Hallo nochmal,

nur so als Tipp:
in solchen Fällen kann der Einzelschritt hilfreich sein. Bei großen
Schleifen ist das natürlich etwas nervig. Wenn ich aber z.B. weiß dass
das Problem erst ab Zeile 200 auftreten kann, nutze ich gern einen
Befehl wie:
If i = 200 then Stop
Dann kann das Makro bis Zeile 200 durchlaufen und springt erst dann
in den Einzelschritt. Jetzt kannst du mit der Maus auf deine Variable
gehen und das Zwischenergebnis auf Korrektheit prüfen. Ist noch alles
OK, prüfst du einfach bei Zeile 400 nochmal. Wenn nicht musst du
vielleicht auf 100 runtergehen.

Gruß Mr. K.
0 Punkte
Beantwortet von ahorn38 Experte (3.2k Punkte)
Hallo Mr.K.
danke für den Tipp - genau so habe ich es gemacht und bin auf folgendes gestoßen:

Set rng1 = LAGB.Columns(8).Find(what:=Cells(i, 5), lookat:=xlWhole)


Bei dieser Befehlszeile tritt der Fehler auf. Wenn im Tabellenblatt LAGB kein Filter gesetzt ist, liefert mir "find" das richtige Ergebnis. Ist ein Filter gesetzt ist rng1=Nothing.
Insofern war mein Problem nicht richtig erklärt: es liegt also nicht daran, daß Zeilen ausgeblendet wurden sondern daran, daß der Filter aktiv war.
Jetzt ist die Frage, ob ich auch bei gesetztem Filter den obigen Code verwenden kann oder wie müßte die Suche dann erfolgen?
Danke und Gruß!
A.
0 Punkte
Beantwortet von
Hallo Andreas,

aha. Genau das ist die Erklärung. Bei aktivem Filter, können über die Such-Funktion ausgeblendete Daten nicht gefunden werden.
Die Excel-Hilfe sagt dazu folgendes:
Wenn Sie Daten suchen, von deren Vorhandensein Sie überzeugt sind, Sie diese Daten aber nicht über das Dialogfeld Suchen und Ersetzen finden können, sind die Daten möglicherweise durch einen angewendeten Filter ausgeblendet. Entfernen Sie den Filter, indem Sie eine Zelle innerhalb des gefilterten Bereichs auswählen, klicken Sie auf der Registerkarte Start in der Gruppe Bearbeiten auf Sortieren und Filtern und klicken Sie dann auf Löschen. Suchen Sie die Daten anschließend erneut.

Interessant ist, dass wenn kein Filter gesetzt ist, über dieselbe Funktion auch manuell ausgeblendete Zeilen angesprungen werden können.
Du musst also überlegen, was dir wichtiger ist:
Willst du den Filter behalten, bleibt dir nur die Option auf die Find-Funktion zu verzichten und wirklich alle Zeilen zu durlaufen um über die Hidden-Eigenschaft zu prüfen ob sie ausgeblendet ist oder nicht.
Möchtest du hingegen die Makro-Laufzeit mithilfe der Find-Funktion verkürzen, dann solltest du den Filter deaktivieren.
Du könntest natürlich auch zu Makrobeginn, den Autofilter auf ungefilterten Status setzen. z.B. mit folgendem Code:
With ActiveSheet.Range("$B$10:$D$13")
For i = 1 To .Columns.Count
.AutoFilter field:=i
Next i
End With
Für Range müsstest du natürlich den Bereich deines Filters angeben.
Mehr fällt mir dazu leider auch nicht ein.
Gruß Mr. K.
0 Punkte
Beantwortet von ahorn38 Experte (3.2k Punkte)
Hallo,

danke für deine Antwort. Am besten würde mir gefallen, den Filter "temorär" zurückzusetzen und nach dem Suchen wieder zu aktivieren. Kennst du eine Möglichkeit, wie man die Filtereinstellung quasi merken kann, um sie später wieder herzustellen?
Danke und Gruß
A.
0 Punkte
Beantwortet von
Hallo Andreas,

Eine Idee hätte ich da tatsächlich noch, die deinem Wunsch schon ziemlich nahe kommt. Leider konnte ich den folgenden Code in meinem alten Excel nur eingeschränkt testen, da eine multiple Filterauswahl erst bei späteren Versionen möglich ist.

In meiner engeschränkten Version läuft er bislang super. Theoretisch sollte er aber auch bei dir laufen und deinem Wunsch entsprechen. Hundertprozentig sicher kann ich da aber leider erst morgen sein. Probiers trotzdem schon mal aus. Vielleicht klappts ja.
Sub FilterwerteDurchsuchen()

Dim Filtersaver() As Variant 'hier werden die Filtereinstellungen gespeichert

'Filter zurücksetzen und Werte speichern
With Worksheets("Tabelle1").AutoFilter
ReDim Filtersaver(.Filters.Count, 3) 'array auf Filtergröße ausweiten

For i = 1 To .Filters.Count 'Anzahl der filterbaren Spalten
If .Filters(i).On Then 'wenn Filter aktiv
Filtersaver(i, 1) = .Filters(i).Criteria1 'Bedingung1 oder Multiauswahl speichern
If Not IsArray(Filtersaver(i, 1)) And .Filters(i).Operator > 0 Then 'wenn zweite Bedingung vorh.
Filtersaver(i, 2) = .Filters(i).Criteria2 'Kriterium2 speichern
Filtersaver(i, 3) = .Filters(i).Operator 'Operator speichern
End If
.Range.AutoFilter Field:=i 'Filter wird auf "Alle" gesetzt
End If
Next i
End With
Stop
'---------------------
'HIER BEGINNT DEIN MAKRO
'---------------------
'
'...
'Set rng1 = LAGB.Columns(8).Find(what:=Cells(i, 5), lookat:=xlWhole)
'...
'
'---------------------
'HIER ENDET DEIN MAKRO
'---------------------

'Filter wieder gemäß den gespeicherten Werten neu setzen
With Worksheets("Tabelle1").AutoFilter
For i = 1 To .Filters.Count 'Anzahl der filterbaren Spalten
If Not IsEmpty(Filtersaver(i, 1)) Then 'Wenn mindestens eine Bedingung gespeichert wurde
.Range.AutoFilter Field:=i, Criteria1:=Filtersaver(i, 1), _
Criteria2:=IIf(IsEmpty(Filtersaver(i, 2)), Null, Filtersaver(i, 2)), _
Operator:=IIf(IsEmpty(Filtersaver(i, 3)), Null, Filtersaver(i, 3))
End If
Next i
End With

ReDim Filtersaver(0) 'Löschen der gespeicherten Filterwerte

End Sub
Den Namen des den Filter enthaltenen Worksheets (hier 2x Tabelle1) musst du natürlich anpassen.

Gruß Mr. K.
0 Punkte
Beantwortet von
Das Stop muss natürlich noch raus.
0 Punkte
Beantwortet von
Ich nochmal,

wahrscheinlich geht das bei multipler Filter-Auswahl erst, wenn du
If Not IsArray(Filtersaver(i, 1)) And .Filters(i).Operator > 0 Then 'wenn zweite Bedingung vorh.
Filtersaver(i, 2) = .Filters(i).Criteria2 'Kriterium2 speichern
Filtersaver(i, 3) = .Filters(i).Operator 'Operator speichern
End If
durch
If Not IsArray(Filtersaver(i, 1)) Then 'wenn zweite Bedingung vorh.
Filtersaver(i, 2) = .Filters(i).Criteria2 'Kriterium2 speichern
End If
If .Filters(i).Operator > 0 Then 'Wenn Operator vorh.
Filtersaver(i, 3) = .Filters(i).Operator 'Operator speichern
End If
ersetzt.

Probiers aus.
0 Punkte
Beantwortet von
Damn! zu viel weggenommen. So lautet der Ersatzcode richtig:
If Not IsArray(Filtersaver(i, 1)) And .Filters(i).Operator > 0 Then 'Wenn Operator vorh.
Filtersaver(i, 2) = .Filters(i).Criteria2 'Kriterium2 speichern
End If
If .Filters(i).Operator > 0 Then 'Wenn Operator vorh.
Filtersaver(i, 3) = .Filters(i).Operator 'Operator speichern
End If
Vielleicht sollte ich mir doch mal Excel 2010 zumindest als Zweitsystem zulegen um sowas besser austesten zu können. Aber wegen solcher Kleinigkeiten noch mal so viel Geld ausgeben? Hmmm...
...