VBA : Schleife über gefilterte Datenzeilen

Forumthread: VBA : Schleife über gefilterte Datenzeilen

NoNet

Hallo VBA-Spezies,
ich habe hier gerade ein merkwürdiges Problem :
In einer Mappe existieren die Tabellenblätter "Liste" und "Formular".
Die "Liste" enthält mehrere Datenzeilen, darunter in Spalte C eine nicht eindeutige "MNr".
Zu jeder MNr. gibt es eine oder mehrere FNr. (Spalte F).
Ich habe nun bereits programmiert, dass bei Aktivierung des Blattes "Formular" in Zelle B1 eine Gültigkeitsliste erscheint, die alle MNr. aus Blatt "Liste" ohne redundante Einträge enthält.
Bei Auswahl einer MNr aus der Gültigkeitsliste soll in Zelle D1 eine weitere Gültigkeitsliste mit allen zu dieser MNr. passenden FNr. erstellt werden. Dies funktioniert auch grundsätzlich (später soll auch der umgekehrte Weg programmiert werden, doch das spielt hier keine Rolle).
Da die Liste mehrere hundert Datensätze beinhalten kann, habe ich das Befüllen der Gültigkeitsliste (im blatt "Formular") nicht per Schleife über alle Datensätze der "Liste" gelöst, sondern die "Liste" zuvor mit der in B1 ausgewählten MNr. gefilter (Autofilter). Es müssen nun also immer nur die sichtbaren Zellen der Spalte in die Gültigkeitsliste aufgenommen werden.
Diese Methode hat sich in meinen Projekten bereits mehrfach bewährt und funktioniert auch wesentlich schneller als eine Schleife über ALLE Datenzeilen !
Um nur die sichtbaren Zeilen (also : Datenzeilen, die dem Autofilter-Kriterium entsprechen) auszuwerten, verwende ich folgende Codezeile (in Zeile 8 der Spalte F steht der erste mögliche Datensatz, MNr. in Spalte C ist in allen Zeilen gefüllt !) :
For Each rngZelle In .Range("F8:F" & .Cells(Rows.Count, 3).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Das funktioniert im aktuellen Projekt auch zu ca. 90% aber manchmal "hängt" die Schleife in einer (fast) endlosen Schleife fest :-(
Der Grund ist offenbar, dass Range("F8:F" & .Cells(Rows.Count, 3).End(xlUp).Row).SpecialCells(xlCellTypeVisible) manchmal falsch interpretiert wird :
Wenn durch das Autofilterkriterium z.B. nur die Zeilen 9 und 11 sichtbar sind, dann sollte obige Anweisung den Bereich F9,F11 liefern - was auch meist funktioniert.
Nur werden leider auch manchmal ganze Zeilen ($9:$9,$11:$11) bzw. gesamte Zellbereiche ($1:$7,$9:$9,$11:$65536) als Ergebnis geliefert, so dass die Schleife jede einzelne Zeile in diesen Bereichen durchsucht, wodurch die scheinbare "Endlosschleife" (so "endlos" ist sie ja gar nicht, da es ja "nur" 16.776.704 Zellen sind ;-) entsteht.
Anbei ein kleines Beispiel mit dem gesamten Code ("Daten - Gültigkeit" in den grünen Feldern des Blattes "Formular") :
https://www.herber.de/bbs/user/62976.xls
Hat jemand eine Erklärung für dieses Verhalten ? - Kennt jemand evtl. eine bessere Lösung, alle sichtbaren Zellen einer gefilterten Tabelle zu durchlaufen ?
Danke und MAAAHLZEIT,
Gruß, NoNet

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: VBA : Schleife über gefilterte Datenzeilen

07.07.2009 13:26:38

ransi

HAllo NoNet
SpecialCells sind so eine Sache....
Die haben mich mal fürchterlich geärgert.
Starte mal die Sub Test():
' **********************************************************************
' Modul: Tabelle1 Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Public Sub test()
Dim rng As Range
Set rng = Tabelle1.Range("A:A").Cells.SpecialCells(xlCellTypeBlanks)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "Angeschubst"
End Sub

Seitdem versuche ich specialcells zu meiden.
Zu deinem Problem:
Stell den Datenbereich fest.
DAnn eine Schleife darüber

Dim r As Range
Dim Datenbereich As Range
Set Datenbereich = Range("F8:F1000") 'Beispiel
For Each r In Datenbereich.Rows
    If r.RowHeight = 0 Then
        MsgBox "Gefiltert"
        Else:
        MsgBox "ungefiltert"
    End If
Next

Ist evtl. nicht so schnell wie SpecialCells, aber dafür zuverlässig.
ransi

AW: VBA : Schleife über gefilterte Datenzeilen

07.07.2009 13:56:08

Luschi

Hallo Nonet,
ich benutze folgendes Konstukt:
Dim n As Long
n = n = Worksheets("Liste").AutoFilter.Range.Columns(6).Cells.Count
For Each rngZelle In .AutoFilter.Range.Columns("F").Offset(1, 0).Resize(n - 1, 1).SpecialCells(xlCellTypeVisible)
Gruß von Luschi
aus klein-Paris

Infobox / Tutorial

Schleifen über gefilterte Daten mit VBA in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Aktiviere das VBA-Fenster:
- Drücke ALT + F11, um den VBA-Editor zu öffnen.
Erstelle ein neues Modul:
- Klicke mit der rechten Maustaste auf "VBAProject (DeineDatei.xlsm)" und wähle "Modul einfügen".

Füge den folgenden Code ein:

Public Sub SchleifeDurchGefilterteDaten()
   Dim rngZelle As Range
   Dim n As Long

   ' Bestimme die Anzahl der gefilterten Zellen
   n = Worksheets("Liste").AutoFilter.Range.Columns(6).Cells.Count

   ' Durchlaufe die gefilterten Zellen in Spalte F
   For Each rngZelle In Worksheets("Liste").AutoFilter.Range.Columns("F").Offset(1, 0).Resize(n - 1, 1).SpecialCells(xlCellTypeVisible)
       ' Hier kannst Du Deine Logik einfügen
       Debug.Print rngZelle.Value
   Next rngZelle
End Sub

Teste den Code:
- Stelle sicher, dass das Tabellenblatt "Liste" einen Autofilter auf Spalte F hat.
- Führe das Makro aus, um die gefilterten Daten zu durchlaufen.

Häufige Fehler und Lösungen

Fehler: Endlosschleife beim Durchlaufen
Wenn Du auf eine Endlosschleife stößt, könnte es daran liegen, dass SpecialCells nicht die erwarteten sichtbaren Zellen zurückgibt. Stelle sicher, dass der Autofilter korrekt angewendet wurde und keine leeren Zellen im gefilterten Bereich vorhanden sind.
Lösung: Überprüfe den Bereich, den Du mit SpecialCells ansprichst. Eine Alternative ist, den Datenbereich festzulegen und die Schleife über alle Zeilen in diesem Bereich laufen zu lassen, wie in der Antwort von ransi beschrieben.

Alternative Methoden

Wenn Du Probleme mit SpecialCells hast, kannst Du eine andere Methode verwenden:

Dim r As Range
Dim Datenbereich As Range
Set Datenbereich = Range("F8:F1000") ' Definiere den Datenbereich

For Each r In Datenbereich.Rows
    If r.RowHeight > 0 Then
        ' Verarbeite die sichtbaren Zellen
        Debug.Print r.Cells(1, 1).Value
    End If
Next r

Diese Methode ist zwar etwas langsamer, aber zuverlässiger, da sie keine unsichtbaren Zellen berücksichtigt.

Praktische Beispiele

Hier sind einige Beispiele, wie Du die Schleife nutzen kannst:

Zählen der sichtbaren Zellen:

Dim countVisible As Long
countVisible = 0
For Each rngZelle In Worksheets("Liste").AutoFilter.Range.Columns("F").SpecialCells(xlCellTypeVisible)
   countVisible = countVisible + 1
Next rngZelle
MsgBox "Anzahl der sichtbaren Zellen: " & countVisible

Kopieren der sichtbaren Zellen in ein anderes Blatt:

Dim ZielBlatt As Worksheet
Set ZielBlatt = Worksheets("Formular")
Dim ZielZeile As Long
ZielZeile = 1

For Each rngZelle In Worksheets("Liste").AutoFilter.Range.Columns("F").SpecialCells(xlCellTypeVisible)
   ZielBlatt.Cells(ZielZeile, 1).Value = rngZelle.Value
   ZielZeile = ZielZeile + 1
Next rngZelle

Tipps für Profis

Vermeide SpecialCells, wenn möglich: Wenn Du sicherstellen kannst, dass Dein Datenbereich nicht leer ist, kann eine Schleife über die Zeilen sicherer sein.
Nutze Debugging: Verwende Debug.Print, um den Verlauf Deiner Schleife zu verfolgen und Probleme frühzeitig zu erkennen.
Optimiere Deine Datenstruktur: Halte Deine Daten sauber und gut strukturiert, um die Performance zu verbessern.

FAQ: Häufige Fragen

1. Warum funktioniert SpecialCells nicht wie erwartet?
SpecialCells kann manchmal leere oder nicht sichtbare Zellen zurückgeben, wenn der Autofilter nicht korrekt angewendet wurde oder wenn es leere Zellen im Bereich gibt.

2. Wie kann ich die Performance beim Durchlaufen großer Datenmengen verbessern?
Vermeide Schleifen über alle Zeilen und nutze stattdessen gezielte Bereiche oder Filter, um die Anzahl der zu verarbeitenden Zellen zu reduzieren.

Beliebteste Forumthreads (12 Monate)

VBA : Schleife über gefilterte Datenzeilen

Forumthread: VBA : Schleife über gefilterte Datenzeilen

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Schleifen über gefilterte Daten mit VBA in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

VBA : Schleife über gefilterte Datenzeilen

Forumthread: VBA : Schleife über gefilterte Datenzeilen

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Schleifen über gefilterte Daten mit VBA in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread