Große Datenmengen in VBA: Arrays, Dictionary & AutoFilter richtig einsetzen

Wenn Zehntausende bis Millionen Zeilen verarbeitet werden sollen, zählt jede Millisekunde. In diesem Leitfaden zeige ich, wie Sie Arrays, Scripting.Dictionary und AutoFilter kombinieren, um große Daten schnell zu lesen, zu transformieren und zu aggregieren – inklusive Praxisfall.

---

1) Grundprinzip: IO minimieren, CPU nutzen

• Range-Zugriffe sind teuer (COM-Overhead). Daten einmal einlesen, im Speicher bearbeiten, einmal zurückschreiben.
• In VBA zählen einfache, lineare Algorithmen und Datenstrukturen.

---

2) Arrays – der Turbo für Massenoperationen

Dim arr As Variant
arr = Range("A2:D100000").Value ' einlesen

Dim r As Long
For r = 1 To UBound(arr, 1)
    ' Beispiel: Trim auf Spalte 1
    arr[r, 1] = Trim$(CStr(arr[r, 1]))
Next r

Range("A2:D100000").Value = arr ' zurück

---

3) Dictionary – schnelle Lookups & Gruppierung

' Referenz: Microsoft Scripting Runtime (optional)
Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")

Dim key As String
For r = 1 To UBound(arr, 1)
    key = CStr(arr[r, 1]) ' z. B. Kundennr
    If Not dic.Exists(key) Then dic(key) = 0#
    dic(key) = dic(key) + CDbl(arr[r, 4]) ' Umsatz aufsummieren
Next r

• O(1) Zugriffe im Schnitt
• Eignet sich für Dublettenerkennung, Aggregation, Mapping

---

4) AutoFilter & AdvancedFilter – Blattseitige Massenoperationen

• AutoFilter: sichtbar → exportierbar (Copy Visible Cells).
• AdvancedFilter: einzigartig/ohne Duplikate extrahieren.

With Range("A1:D100000")
    .AutoFilter Field:=3, Criteria1:="DE"
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("DE").Range("A1")
    .AutoFilter
End With

---

5) Praxisfall – Kundensales aggregieren (500k Zeilen)

Ziel: Summe Umsatz pro Kunde, nur DE-Kunden, Ergebnis in neues Blatt.

Lösungsskizze

Sub AggregiereSales()
    Dim src As Variant, r As Long, dic As Object, key As String
    Dim ws As Worksheet: Set ws = Sheets("Sales")
    src = ws.Range("A2:D" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).Value

    Set dic = CreateObject("Scripting.Dictionary")
    For r = 1 To UBound(src, 1)
        If src[r, 3] = "DE" Then
            key = CStr(src[r, 1]) ' Kundennr
            If Not dic.Exists(key) Then dic(key) = 0#
            dic(key] = dic[key] + CDbl(src[r, 4]) ' Umsatz
        End If
    Next r

    ' Ausgabe
    Dim out() As Variant, i As Long
    ReDim out(1 To dic.Count, 1 To 2)
    i = 1
    Dim k As Variant
    For Each k In dic.Keys
        out[i, 1] = k
        out[i, 2] = dic(k)
        i = i + 1
    Next k
    Sheets("Ergebnis").Range("A2").Resize(dic.Count, 2).Value = out
End Sub

Effekt: Minuten statt Stunden, keine Zell-für-Zell-Schleifen.

---

6) Checkliste – für große Datenmengen

---

Fazit

Große Datenmengen sind mit VBA machbar, wenn Sie IO minimieren und geeignete Datenstrukturen einsetzen. Kombinieren Sie Arrays, Dictionary und Filter – und messen Sie Ihren Gewinn.