Mehrdimensionalen Array verwenden

26.02.2025 17:06:51

Georg

Liebe Mitglieder, der Code macht das, was er soll. Meine Frage: im Laufe des Jahres wächst das Blatt "Daten" ziemlich an (ca. 50.000 Zeilen) und wird ziemlich langsam. Kann man das mit einem array beschleunigen? Wenn ja wie? ich habe es mit redim etc noch nicht so ganz kapiert. ein kleiner Tipp wäre prima ! Danke Georg

Dim loLetzte As Long, loSpalte As Long

Application.ScreenUpdating = False



With Worksheets("Daten")

    loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row

    loSpalte = .Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1).Column

    .Cells(1, loSpalte) = 1

    With .Range(.Cells(2, loSpalte), .Cells(loLetzte, loSpalte))

        .FormulaLocal = "=WENN(ZÄHLENWENN(Namen!A:A;E2)>0;1;ZEILE())"

        .Value = .Value

    End With

    .Range(.Cells(1, 1), .Cells(loLetzte, loSpalte)).RemoveDuplicates Columns:=loSpalte, Header:=xlNo

    .Columns(loSpalte).ClearContents

End With



Application.ScreenUpdating = True

End Sub

AW: Mehrdimensionalen Array verwenden

26.02.2025 17:09:57

Onur

Poste eine entsprechende Datei mit genauer Beschreibung deiner Wünsche (möglichst mit Wunschergebnis).
Warum vermischst du eigentlich VBA mit Excel? Wieso musst du überhaupt mit VBA Formeln in Zellen eintragen?

AW: Mehrdimensionalen Array verwenden

26.02.2025 17:39:15

Marc





Dim arrayVar () as String

Dim i as Integer, j as Integer, k as Integer



i = 25

j = 30

k = 5



ReDim ArrayVar (i,j,k)

Damit hast du ein 3 Dimensionales Array angepasst
wichtig das das Array in der Dim Definition keine Werte enthält bzw. keine Dimensionen

AW: Mehrdimensionalen Array verwenden

26.02.2025 18:10:03

Daniel

Hi
Mit einem Array beschleunigt man hautsächlich Vorrgänge, die in einer Schleife jede Zeile einzeln verarbeiten. Das machst du hier aber nicht, du verwendest eigentlich schon die Zeitoptimierte Schleifenfreie Methode zum Löschen von Zeilen mit Bedingung (und das finde ich sehr gut von dir)

Dein Problem sind die 50.000 ZählenWenns, die jedesmal die Spalte A durchsuchen müssen, und wenn die die auch soviele Einträge enthält, wird es zäh, weil Excel dann intern 50.000 × 50.000 Zellvergleiche durchführen muss.

Zum Beschleunigen könnte man hier tatsächlich ein Array einsetzten, aber nur in Kombination mit einer fortgeschrittenen Programmiertechnik, dem Dictionary.

Es geht aber auch einfacher, mir folgenden zwei Schritten.

1. Sorge dafür dass Namen!A:A aufsteigend sortiert ist und der erste Wert eine 0 ist.
Das auch im Makro selbst machen.

2. Verwenden zur Prüfung, ob der Wert aus Spalte E in Spalte A vorhanden ist, diese Formel:

=Wenn(SVerweis(E2;Namen!A:A;1;wahr)=E2;1;Zeile())

Aufgrund der Sortierung kann hier die schnellere Variante des SVerweises verwendet werden (binäre Suche)

Die 0 als erster Wert von Spalte wird nenötigt, weil du sonst einen #NV-Fehler bekommst, wenn du nach einem Namen suchst, der kleiner ist als der kleinste Namen in Spalte A.

Gruß Daniel

AW: Mehrdimensionalen Array verwenden

27.02.2025 15:03:23

Yal

Moin,

ich habe die Idee der Dictionary aufgegriffen und meine Mittagspause damit belustigt:

Sub Duplikate_entfernen()

Dim LetzteZelle As Range

Dim Dic As Object

Dim Z



    Application.ScreenUpdating = False

    Set Dic = CreateObject("scripting.dictionary")

    With Worksheets("Daten")

        Set LetzteZelle = .Cells(.Rows.Count, 1).End(xlUp)

    'Sammlung aufbauen. Dabei wird immer die ZeilNr des ersten Auftretens gespeichert

        For Each Z In .Range("A2", LetzteZelle)

            If Not Dic.Exists(LCase(Z.Value)) Then Dic(LCase(Z.Value)) = Z.Row

        Next

    'Löschen

        For Z = LetzteZelle.Row To 2 Step -1

            If Z > Dic(LCase(.Cells(Z, 1).Value)) Then Rows(Z).Delete

        Next

    End With

    Set Dic = Nothing

    Application.ScreenUpdating = True

End Sub

Wenn die Daten, die an der Auflistung im Blatt "Daten" hinzugefügt werden, aus einer Datei kommen, könnte es sich lohnen, Power Query anzuschauen.

VG
Yal

AW: Mehrdimensionalen Array verwenden

27.02.2025 22:57:42

Onur

Dieser Code braucht bei mir unter 1,5 sec, um alle Einträge in Spalte E zu überprüfen, ob sie in Spalte A vorkommen.
Dann werden alle Zeilen gelöscht, bei denen das nicht zutrifft.

Private Sub CommandButton1_Click()

    Dim z, s, lz, arr(), x, dict, x0

    Dim ti

    Set dict = CreateObject("Scripting.Dictionary")

    ti = Timer

    lz = 50001

    ReDim arr(0): arr(0) = 1: x0 = 1

    For z = 2 To lz

        x = Cells(z, 1)

        If Not dict.exists(x) Then

            dict.Add x, 1

            Debug.Print x

        End If

    Next

    For z = 2 To lz

        x = Cells(z, 5).Value

        If Not dict.exists(x) Then

            ReDim Preserve arr(x0)

            arr(x0) = z

            x0 = x0 + 1

        End If

    Next z

    For x = UBound(arr) To 0 Step -1

        z = arr(x)

        If z > lz Then

            Range("A" & z + 1 & ":Z" & lz).EntireRow.Delete

        End If

        lz = z - 1

    Next x

    MsgBox Timer - ti

End Sub

AW: Mehrdimensionalen Array verwenden

27.02.2025 23:03:31

Onur

Sorry - muss natürlich heissen: "bei denen das zutrifft".
Und Zeilenanzahl habe ich als 50000 eingesetzt.

Alternative

27.02.2025 15:09:24

Yal

Sub Duplikate_entfernen()

Dim Dic As Object

Dim Z

Dim Key As String



    Application.ScreenUpdating = False

    Set Dic = CreateObject("scripting.dictionary")

    With Worksheets("Daten")

    'Sammeln + bereits gesammelte Schlüsseln-Zeile löschen

        For Z = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1

            Key = LCase(.Cells(Z, 1).Value)

            If Dic.Exists(Key) Then Rows(Dic(Key)).Delete

            Dic(Key) = Z

        Next

    End With

    Set Dic = Nothing

    Application.ScreenUpdating = True

End Sub

VG
Yal

AW: Alternative

27.02.2025 17:43:16

Daniel

Bei 50.000 Zeilen sollte man nicht anfangen, die Zeilen einzeln zu löschen.
Das solle jemand, der hier schon ein paar Jahr aktiv ist, inzwischen gelernt haben.

Ich würde hier ein Ergebnisarray mit der Kennzeichnung zum Löschen erstellen, das Ergebnisarray in eine Spalte schreiben und dann mir Remove duplicates die Zeilen entfernen.

Gruß Daniel

AW: Alternative

27.02.2025 18:56:31

Yal

@Daniel: jo, ein Bischen zu sehr auf dem Dictionary fokussiert...

Ich überspringe das Hilfespalte-Befüllen und sammle direkt die zu löschenden Zeilen. Sollte ein paar Mikrosekunden schneller sein.

Sub Duplikate_entfernen()

Dim Dic As Object

Dim LetzteZelle As Range

Dim ZuLö As Range

Dim Z



    Application.ScreenUpdating = False

    Set Dic = CreateObject("scripting.dictionary")

    With Worksheets("Daten")

        Set LetzteZelle = .Cells(.Rows.Count, 1).End(xlUp)

        Set ZuLö = LetzteZelle.EntireRow(2) 'erste Zeile nach letzter Zeile. Für Union darf ZuLö nicht leer sein

    'Sammeln

        For Each Z In Range("A2", LetzteZelle)

            If Dic.Exists(LCase(Z.Value)) Then

                Set ZuLö = Union(ZuLö, Z.EntireRow)

            Else

                Dic(LCase(Z.Value)) = 1

            End If

        Next

    'Löschen

        ZuLö.Delete

    End With

    Set Dic = Nothing

    Application.ScreenUpdating = True

End Sub

VG
Yal

AW: Alternative

27.02.2025 19:45:52

Daniel

das ist immer noch noch nicht wirklich schnell, bzw es kommt im diesen Fall darauf an, wie die Daten angeordnet sind.
das Problem in Excel ist, dass die Zeit, die für das gemeinschaftliche Löschen von Zeilen (so wie du es machst) benötigt wird, nicht dadurch bestimmt wird, wie viele Zeilen gelöscht werden, sondern aus wie vielen Areas (lückenlos zusammenhängende rechteckige Zellblöcke) der Zellbereich besteht.
vereinfacht formuliert:
das Löschen der Range("1:100000") dauert genauso lang wie das Löschen der Range("1:1"), weil in beiden Fällen nur eine Area vorliegt.
das Löschen der Range("1:1,3:3") hingegen dauert doppelt so lang, weil des eben zwei Areas sind.

der Grund hierfür ist folgende Hintergrundaktion (welche auch nicht abschaltbar ist sondern immer ausgeführt wird):
Excel prüft beim Löschen von Zeilen, ob es irgendwo in einer der geöffneten Exceldateien Zellbezüge gibt, die auf den zu löschenden Zellbereich referenzieren und dann ggf angepasst werden müssen (der Bezug A1:A10 wird zu A1:A9 wenn eine der Zeilen 1-10 gelöscht wird)
diese Prüfung muss für jede Area getrennt ausgeführt werden, dh je zersplitterter dein Zellbereich ist, um so häufiger muss das gemacht werden.
Ebenso wird die Kennung xlcelltypeLastCell (letzte benutze Zelle im Blatt) mit verschoben

um das Löschen der Zeilen wirklich und maximal zu beschleunigen, gibt es daher zwei Wege:
a) das Löschen der Zeilen mit Duplikate-Entfernen, denn beim Duplikate Entfernen wird diese Hintergrundaktion nicht ausgeführt
b) vor dem Löschen die Zeilen so sortieren, dass die zu löschenden Zeilen einen lückenlosen Zellblock bilden und somit in einem Arbeitsgang gelöscht werden können.
wobei b) bei richtig großen Datenmengen auch nochmal spürbar schneller ist als a), und auch das einmalige Sortieren geht bei großen Datenmengen sehr schnell.

in Älteren Excelversionen (vor 2007) gab es zusätzlich das Problem, dass die Anzahl der Areas, die so in einem Arbeitsgang bearbeitet werden konnten, auf 8192 limitiert war, dh so bald die Liste mehr als das doppelte dieser Zahl an Zeilen aufwies bestand das Risiko, dass das Löschen von Zeilen in einen Arbeitsgang nicht mehr vollständig ausgeführt wurde (wenn du z.B die Zeilen, die stehenbleiben sollen, per Autofilter ausgeblendet hast)
davon betroffen natürlich neben Zellauswahl über Autofilter, auch SpecialCells oder eben Union.
mit den neuen Excelversionen wurde das Limit zwar erweitert, aber ob es ganz aufgehoben wurde, weiß ich nicht.

Gruß Daniel

AW: Alternative

27.02.2025 23:24:49

Onur

Hallo Yal,

Dein Makro ist zwar auch schnell, aber - im Gegensatz zum Originalmakro - löscht es nicht alle Zeilen, bei denen der Text aus Spalte E in irgend einer Zelle in Spalte A vorkommt.

Gruß
Onur

AW: Alternative - Korrektur

28.02.2025 10:59:52

Yal

oha! das habe ich übersehen.

Neuer Versuch:

Sub Duplikate_entfernen()

Dim Dic As Object

Dim Z



    Application.ScreenUpdating = False

    Set Dic = CreateObject("scripting.dictionary")

    With Worksheets("Daten")

    'Sammeln + bereits gesammelte Schlüsseln-Zeile löschen

        For Z = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1

            If Dic.Exists(LCase(.Cells(Z, "E").Value)) Then Rows(Dic(LCase(.Cells(Z, "E").Value))).Delete

            Dic(LCase(.Cells(Z, "A").Value)) = Z

        Next

    End With

    Set Dic = Nothing

    Application.ScreenUpdating = True

End Sub

Was Performance angeht, kann man natürlich optimieren. Ich wäre aber dafür der gesamte Vorgang zu hinterfragen und nur die Datensätze hinzufügen, die nicht anschliessend gelöscht werden sollten, zu laden (u.a. mit PQ):
Ich habe im ersten Stand die Einträge a, b, c. Ich lade dazu b, c, d, und löche anschliessend die zweiten b und c. Warum nicht von Anfang an nur d laden?

VG
Yal

AW: Mehrdimensionalen Array verwenden

26.02.2025 17:18:16

Georg

Weil ich es einfach nicht besser weiß G!

AW: Mehrdimensionalen Array verwenden

26.02.2025 17:20:15

Onur

Dann poste endlich die Datei...

AW: Mehrdimensionalen Array verwenden

26.02.2025 17:29:44

Georg

Geht leider nicht, die Datei ist deutlich umfangreicher mit 5 Modulen, welche sukzessive abgearbeitet werden müssen mit Zugriffe auf Laufwerksdateien etc. Gibt es einen Tipp für Seiten im Internet, zum Thema array, die ihr empfehlen könnt? Danke G

AW: Mehrdimensionalen Array verwenden

26.02.2025 18:10:10

Onur

Dann beschreibe wenigstens GENAU, was der Code machen soll.

AW: Mehrdimensionalen Array verwenden

26.02.2025 21:19:54

Onur

Dann poste wenigstens mal eine Datei mit 20-30 Zeilen mit imaginären Datensätzen. Hauptsache, deine Formeln sind drin und du erklärst (zur Sicherheit) genau, was du erreichen willst.
Runterkopieren kann ich mir Datensätze dann selber.
Warum hast du eigentlich auch die Spaltenanzahl im Makro dynamisch gemacht? Kommen denn auch neue Spalten dazu ?

AW: Mehrdimensionalen Array verwenden

26.02.2025 18:35:09

daniel

Nun, das geht aus diesem Code doch Recht eindeutig hervor (der ist ja nicht so komplizert)
Es sollen alle Zeilen gelöscht werden, bei denen der Wert aus Spalte E in der Liste Namen!A:A vorhanden ist, so dass nur die neuen übrig bleiben.

Btw, weil du gefragt hast, die Kombination aus Formeln und Excelmethoden (Menüfunktionen) in einen VBA-Makro ist eine sehr gute Möglichkeit, um schnelle und effiziente Makros zur Bearbeitung von Excellisten zu programmieren, vor allem dann, wenn man nur Basiskenntnisse in VBA hat oder sich mit dem abstrakten Vorstellen, was da im Makro passiert, noch Schwierigkeiten hat.
Ein Vorteil ist, dass das Makro dann häufig nur eine lineare Abfolge von Befehlen ist, ohne Schleifen und Verzweigungen.
Ein weiterer Vorteil ist, dass man Ablauf auch von Hand (also ohne Makro) ausführen kann, was das ganze nicht nur wesentlich anschaulicher macht, sondern man kann sich in vielen Fällen auch vom Recorder bei der Programmierung helfen lassen .

Gruß Daniel

AW: Mehrdimensionalen Array verwenden

26.02.2025 18:44:19

Onur

Dann kann er das Ganze doch einfach auf einem anderen Blatt filtern:

=FILTER(A2:J20000;(ZÄHLENWENN(A2:A18;E2:E20000)=0)*(A2:A20000>""))

Habe mal angebnommen, dass das Ganze bis Spalte J und Zeile 20.000 geht).

AW: Mehrdimensionalen Array verwenden

26.02.2025 18:45:49

Onur

Blattnamen müsste er noch hinzufügen....

AW: Mehrdimensionalen Array verwenden

26.02.2025 18:56:12

Onur

Und deswegen brauche ich die Datei (weil er das kaum alleine einbauen kann)...

Mehrdimensionalen Array verwenden

Forumthread: Mehrdimensionalen Array verwenden

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Mehrdimensionalen Array verwenden

Forumthread: Mehrdimensionalen Array verwenden

21 Beiträge zum Forumthread Beiträge zu diesem Forumthread

21
Beiträge zum Forumthread

Beiträge zu diesem Forumthread