Mehrere Zeilen markieren

[Peter Wenzel]

Hallo,

ich möchte per VBA in abhängigkeit bestimmter Bedingungen Zeilen
markieren, um diese dann zu löschen.
Konkret: ich habe eine Schleife gebildet die abfragt ob eine Zelle (z.B.
in Spalte E) einen Wert enthält.
Wenn nicht soll diese Zeile markiert werden. Dann geht's weiter zur
nächsten Zeile...

Eine Zeile bekomme ich markiert (ActiveCell.Offset(z,
0).EntireRow.Select). Beim nächsten Durchlauf aber wird dann nur die
nächste Zeile markiert. Die erste, und im Weiteren andere, sollen aber
auch markiert werden, damit ich diese dann in einem Rutsch löschen kann.

Kann mir jemand dabei weiterhelfen?

Vielen Dank
Peter

[Claus Busch]

Hallo Peter,

Post by Peter Wenzel
ich möchte per VBA in abhängigkeit bestimmter Bedingungen Zeilen
markieren, um diese dann zu löschen.
Konkret: ich habe eine Schleife gebildet die abfragt ob eine Zelle (z.B.
in Spalte E) einen Wert enthält.
Wenn nicht soll diese Zeile markiert werden. Dann geht's weiter zur
nächsten Zeile...
Eine Zeile bekomme ich markiert (ActiveCell.Offset(z,
0).EntireRow.Select). Beim nächsten Durchlauf aber wird dann nur die
nächste Zeile markiert. Die erste, und im Weiteren andere, sollen aber
auch markiert werden, damit ich diese dann in einem Rutsch löschen kann.

lösche die Zeilen doch gleich innerhalb des Schleifendurchlaufs. hier der
Code für leere Zellen in Spalte E (sonst Bezüge anpassen):

Sub Loeschen()

Dim i As Long
Dim LRow As Long

LRow = Cells(Rows.Count, 5).End(xlUp).Row
For i = LRow To 2 Step -1
If Cells(i, 5).Value = "" Then
Rows(i).Delete
End If
Next

End Sub


Mit freundlichen Grüssen
Claus Busch

[Peter Wenzel]

Post by Claus Busch
lösche die Zeilen doch gleich innerhalb des Schleifendurchlaufs. hier der

Hallo Claus,

Ja, das war auch mein erster Versuch. Problem an dieser Vorgehensweise:
Die Tabelle die überprüft werden soll ist nicht immer gleich groß, daher
überprüfe ich sie, und damit die Anzahl der Schleifen, mit "For z = 2 To
ActiveSheet.UsedRange.Rows.Count".
Wenn ich die Zeilen also direkt lösche kommt meine Zählvariable
durcheinander, d.h. die nachfolgenden Zeilen "rutschen" auf, und es
werden Zeilen gelöscht die nicht gelöscht werden sollen.
Daher mein Plan, das Löschen erst zum Schluß vorzunehmen.

Außerdem sind die Zellen nicht leer. Sie werden verglichen mit dem
Inhalt eines Arrays.
Hier mal ein wenig Code mit dem ich Arbeite:

Sheets("ZBN Liste").Activate
For z = 2 To ActiveSheet.UsedRange.Rows.Count
y = 0
For j = 0 To i
If Cells(z, 6) = Beilage(j) Then
y = y + 1
End If
Next j
If y = 0 Then
ActiveCell.Offset(z, 0).EntireRow.Select
End If
Next z
For Each zeile In Selection
ActiveCell.EntireRow.Delete
Next

i = Anzahl der Einträge in Beilage()
z= Zeilennummer der Tabelle
y= ist <>0 wenn ein Eintrag in Beilage() = Eintrag in Spalte 6

Beilage() enthält einen oder mehrere Einträge. (diese werden im Vorfeld
aus einer Listbox ausgelesen.)

Ich hoffe mein Anliegen wird nun etwas deutlicher.

Vielen Dank
Peter

[Claus Busch]

Hallo Peter,

Post by Peter Wenzel
Die Tabelle die überprüft werden soll ist nicht immer gleich groß, daher
überprüfe ich sie, und damit die Anzahl der Schleifen, mit "For z = 2 To
ActiveSheet.UsedRange.Rows.Count".
Wenn ich die Zeilen also direkt lösche kommt meine Zählvariable
durcheinander, d.h. die nachfolgenden Zeilen "rutschen" auf, und es
werden Zeilen gelöscht die nicht gelöscht werden sollen.
Daher mein Plan, das Löschen erst zum Schluß vorzunehmen.

beim Löschen soll man wegen der Zählvariable *immer* von unten nach oben
löschen. Dadurch kommt deine Zählvariable nicht durcheinander. Bei mir im
Code wird zuerst die letzte belegte Zeile ermittelt und dann von unten nach
oben gelöscht.


Mit freundlichen Grüssen
Claus Busch

[Claus Busch]

Hallo Peter,

dann probiers mal so:

With Sheets("ZBN Liste")

LRow = .Cells(Rows.Count, 6).End(xlUp).Row

For i = LRow To 2 Step -1
y = 0
For j = LBound(Beilage) To UBound(Beilage)
If Cells(i, 6) = Beilage(j) Then
y = y + 1
End If
Next j

If y = 0 Then
Rows(i).Delete
End If

Next i

End With



Mit freundlichen Grüssen
Claus Busch

[Peter Wenzel]

Post by Claus Busch
Hallo Peter,

Jepp, so geht's.
Klasse, vielen Dank. Auf jeden Fall werde ich mir merken, daß man beim
Löschen von hinten anfängt.

Bis bald, du hast mir wieder einmal sehr geholfen.

Gruß
Peter

[Claus Busch]

Hallo Peter,

Post by Peter Wenzel
Jepp, so geht's.
Klasse, vielen Dank. Auf jeden Fall werde ich mir merken, daß man beim
Löschen von hinten anfängt.

gern geschehen. Schön, dass es nun wie gewünscht läuft. Danke für die
Rückmeldung.


Mit freundlichen Grüssen
Claus Busch

[Eberhard Funke]

Post by Peter Wenzel
Klasse, vielen Dank. Auf jeden Fall werde ich mir merken, daß man beim
Löschen von hinten anfängt.

Hallo Peter,

(nur zur Info) es geht auch von vorne:

With Sheets("ZBN Liste")
Set rngLösch = .Cells(Rows.Count, 6)
LRow = .Cells(Rows.Count, 6).End(xlUp).Row
For i = 2 To LRow
For j = LBound(Beilage) To UBound(Beilage)
If .Cells(i, 6) = Beilage(j) Then
Set rngLösch = Union(rngLösch, .Cells(i, 6))
Exit For
End If
Next j
Next i
End With
rngLösch.EntireRow.Delete

Die zu löschenden Zellen werden mit der Union-Methode zu *einem* Range
zusammengefasst und dann in der letzen Zeile gelöscht.

Ein kleiner Trick: Um Union anwenden zu können, braucht man einen
"Startbereich", dem man dann die anderen Ranges zufügt. Als Startbereich
habe ich die unterste Zelle in Spalte G genommen (in der Annahme, dass da
keine sensiblen Daten mehr stehen): Set rngLösch = .Cells(Rows.Count, 6)

[Peter Wenzel]

Hallo Eberhard,
danke für den Tipp, habe in einem anderen Zusammenhang von Union
gelesen. Da ich aber z.zt. an meinem ersten VBA-Projekt arbeite konnte
ich das nicht umsetzen.
Werde mir aber auch deinen Tipp merken!

Gruß
Peter

Post by Claus Busch

Post by Peter Wenzel
Klasse, vielen Dank. Auf jeden Fall werde ich mir merken, daß man beim
Löschen von hinten anfängt.

Hallo Peter,
With Sheets("ZBN Liste")
Set rngLösch = .Cells(Rows.Count, 6)
LRow = .Cells(Rows.Count, 6).End(xlUp).Row
For i = 2 To LRow
For j = LBound(Beilage) To UBound(Beilage)
If .Cells(i, 6) = Beilage(j) Then
Set rngLösch = Union(rngLösch, .Cells(i, 6))
Exit For
End If
Next j
Next i
End With
rngLösch.EntireRow.Delete
Die zu löschenden Zellen werden mit der Union-Methode zu *einem* Range
zusammengefasst und dann in der letzen Zeile gelöscht.
Ein kleiner Trick: Um Union anwenden zu können, braucht man einen
"Startbereich", dem man dann die anderen Ranges zufügt. Als Startbereich
habe ich die unterste Zelle in Spalte G genommen (in der Annahme, dass da
keine sensiblen Daten mehr stehen): Set rngLösch = .Cells(Rows.Count, 6)

[Alexander Wolff]

Hallo Eberhard, das sieht gut aus. Hast Du mal einen Test mit bspw. 10.000
Zeilen gemacht, wie da die verschiedenen Ansätze abschneiden? Falls ja,
würden mich die Zeiten interessieren.

[Eberhard Funke]

Post by Alexander Wolff
Hallo Eberhard, das sieht gut aus. Hast Du mal einen Test mit bspw. 10.000
Zeilen gemacht, wie da die verschiedenen Ansätze abschneiden? Falls ja,
würden mich die Zeiten interessieren.

Hallo Alexander,

nein, ich habe das Zeitverhalten nicht getestet und werde jetzt auch nicht
dazu kommen, da ich im Begriff bin, für einen Monat abzutauchen.
Danke auch für Deine Anerkennung an anderer Stelle hier im Thread.

[Alexander Wolff]

Post by Eberhard Funke

Post by Alexander Wolff
Hallo Eberhard, das sieht gut aus. Hast Du mal einen Test mit bspw.
10.000 Zeilen gemacht, wie da die verschiedenen Ansätze abschneiden?
Falls ja, würden mich die Zeiten interessieren.

nein, ich habe das Zeitverhalten nicht getestet und werde jetzt auch
nicht dazu kommen, da ich im Begriff bin, für einen Monat abzutauchen.
Danke auch für Deine Anerkennung an anderer Stelle hier im Thread.

Bitte schön. Dann wenigstens die Fotos von Haien, Korallen und den
Mittaucherinnen bei Rückkehr. Und nimm genug Sauerstoff mit nach unten. Wir
brauchen Dich noch.

[Alexander Wolff]

Post by Peter Wenzel
ich möchte per VBA in abhängigkeit bestimmter Bedingungen Zeilen
markieren, um diese dann zu löschen.
Konkret: ich habe eine Schleife gebildet die abfragt ob eine Zelle
(z.B. in Spalte E) einen Wert enthält.
Wenn nicht soll diese Zeile markiert werden. Dann geht's weiter zur
nächsten Zeile...
Eine Zeile bekomme ich markiert (ActiveCell.Offset(z,
0).EntireRow.Select). Beim nächsten Durchlauf aber wird dann nur die
nächste Zeile markiert. Die erste, und im Weiteren andere, sollen aber
auch markiert werden, damit ich diese dann in einem Rutsch löschen kann.

Mach es doch schneller mittels Autofilter (Tabelle sollte autofilterfähig
sein, also z.B. Bezeichner haben und zusammenhängend wie .CurrentRegion
sein. Fall letzteres fehlt, hängst Du eine gefüllte Spalte an):

- Blattkopie erstellen und umbenennen
- Auf Kopie: Du fragst "E leer" ab und
kopierst dann das ganze Blatt (z.B. A:AZ) über das Original (A1)
- Blattkopie löschen.

Geht 1000mal schneller.

[Alexander Wolff]

Post by Alexander Wolff
- Auf Kopie: Du fragst "E leer" ab und

"E Nichtleere" sollte es heißen

[Alexander Wolff]

Ich sehe grad, dass die Zeilen mit einem Array verglichen werden sollen,
statt dass sie einfach gefüllt oder leer sind. Auch dies geht schnell: Du
kannst ein Array ohne Schleifen direkt einem Range zuweisen,
www.xxcl.de/0052.htm.

Um auch die Schleifen für die Löschvariable zu vermeiden, könnte man

Range("G:G") = Beilage

nehmen und dann in Excel

Range("G:G") von Range("F:F") subtrahieren.

Nur Nullwerte werden dann gefiltert.

[Peter Wenzel]

Post by Alexander Wolff
Ich sehe grad, dass die Zeilen mit einem Array verglichen werden sollen,
statt dass sie einfach gefüllt oder leer sind. Auch dies geht schnell: Du
kannst ein Array ohne Schleifen direkt einem Range zuweisen,
www.xxcl.de/0052.htm.
Um auch die Schleifen für die Löschvariable zu vermeiden, könnte man
Range("G:G") = Beilage
nehmen und dann in Excel
Range("G:G") von Range("F:F") subtrahieren.
Nur Nullwerte werden dann gefiltert.

Hallo Alexander,

vielen Dank für deinen Ansatz, mein Problem war nur, daß das Array über
ein Listfeld (Auswahl) in einem Userform gefüllt wurde.
D.h. ich habe eine Tabelle in der ungeordnet verschiedene Text-Inhalte
stehen, die ich dann über eine "Zwischentabelle" sortiere, dann werden
beim Einlesen in das Listfeld Dubletten herausgefiltert und der Rest
angezeigt. Mittels Multiselect übergebe ich dann meine gewünschten Werte
an das Array "Beilagen()", wo dann in der Ausgangstabelle überprüft
wird, ob die von mir ausgewählten Inhalte in der Tabelle stehen, wenn
nein, soll die Zeile gelöscht werden, so daß nur noch meine ausgewählten
Werte übrig bleiben.
Mit der Lösung vom Claus Busch funktioniert das alles einwandfrei.
Trotzdem möchte ich mich für deine Beteiligung herzlich bedanken.

Gruß
Peter

[Alexander Wolff]

Post by Peter Wenzel
Mit der Lösung vom Claus Busch funktioniert das alles einwandfrei.
Trotzdem möchte ich mich für deine Beteiligung herzlich bedanken.

Danke für ausführliche Rückmeldung! :-)

[Eberhard Funke]

Post by Peter Wenzel
ich möchte per VBA in abhängigkeit bestimmter Bedingungen Zeilen
markieren, um diese dann zu löschen.
Konkret: ich habe eine Schleife gebildet die abfragt ob eine Zelle (z.B.
in Spalte E) einen Wert enthält.
Wenn nicht soll diese Zeile markiert werden. Dann geht's weiter zur
nächsten Zeile...
Eine Zeile bekomme ich markiert (ActiveCell.Offset(z,
0).EntireRow.Select). Beim nächsten Durchlauf aber wird dann nur die
nächste Zeile markiert. Die erste, und im Weiteren andere, sollen aber
auch markiert werden, damit ich diese dann in einem Rutsch löschen kann.

Hallo Peter,

muss es mit Schleife sein?
Sonst:

Columns("E").SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete

[Alexander Wolff]

Post by Eberhard Funke
Columns("E").SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete

Super! Für mich als VBA-Laien eine Erleuchtung.

Nun ist ja auch bald Weihnachten ...

[Peter Wenzel]

Post by Eberhard Funke
muss es mit Schleife sein?
Columns("E").SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete

Hallo Eberhard,

ich denke ich komme um eine "Schleifenlösung" nicht herum, siehe Antwort
an Alexander Wolff.

Vielen Dank
Gruß
Peter