Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1432to1436
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Range innerhalb For each RG Schleife neu zuweisen

Range innerhalb For each RG Schleife neu zuweisen
23.06.2015 07:50:04
Roland
Hallo, ich habe eine Frage und hoffe mir kann jemand hierbei helfen.
ich arbeite eine Tabelle per
"For each RG in Range"
ab und bearbeite jede Zelle.
Zu beginn einer jeden Zeile frage ich jedoch eine bestimmte Zelle auf bestimmte Inhalte ab und wenn dies zutrifft dann loesche ich die komplette Zeile und gehe zu
"next RG"
Nun muss ich jedoch meinen RG auf einen anderen Bereich zuruecksetzen damit ich keine Zeile und vor Allem auch keine Zelle verpasse zu bearbeiten. Bei einer
"For i = x to y"- schleife kann ich den Wert der in i steht einfach auf einen anderen wert zuruecksetzen (z.B. von 100 auf 99) damit die Schleife, wenn ich die Zeile 100 geloescht habe i auf 99 setze und dann bei
"next i"
der wert durch die Schleife automatisch wieder auf 100 gesetzt wird. Somit vermeide ich dass ich beider Abarbeitung nach dem loeschen einer Zeile eine Zeile verpasse.
Ich habe aber keine Ahnung wie ich das mit einer
"For each RG in Range" - Schleife machen kann. Habe schon alles moegliche probiert aber ohne erfolg.
Wie kann ich RG auf einen anderen Wert setzen ?
Kann mir hier jemand helfen ?
Vielen Dank im Voraus.
Roland

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Schleife rückwärts ...
23.06.2015 08:16:06
Matthias
Hallo
Vorschlag:
Benutze eine Schleife die rückwärts läuft
z.B.
for i = 100 to 1 step -1
'hier Deine Bedingung
next
Dann brauchst Du i auch nicht immer neu anpassen.
Gruß Matthias

AW: Range innerhalb For each RG Schleife neu zuweisen
23.06.2015 08:17:35
Werner
Hallo Roland,
genau aus diesem Grund lässt man bei so etwas die Schleife von der letzten Zeile zur ersten Zeile, also rückwärts laufen. Angenommen du willst von der letzten Zeile bis zur Zeile 2 prüfen und löschen. Die ermittelte letzte Zeile hast du in einer Variablen loLetzte stehen. Dann so:
For i = loLetzte to 2 step -1
Gruß Werner

Anzeige
AW: Range innerhalb For each RG Schleife neu zuweisen
23.06.2015 09:52:00
Roland
Danke fuer Eure bisherigen Antworten aber diese sind nicht die gewuenschte Loesung.
Ich arbeite mit Range und nicht mit Integer. Folgendes Problem:
For Each RG In ActiveSheet.Range("A2:F" & ActiveSheet.UsedRange.Rows.Count)
If Right(Range("G" & RG.Row), 1) = "A" Or Right(Range("H" & RG.Row), 1) = "A" Then
Rows(RG.Row).Delete shift:=xlUp
Else
anderer Code
End If
Next RG
Wenn ich jetzt z.B. RG.Address $F$10 habe und die If-Bedingung zutrifft wird die Zeile 10 geloescht. Die Schleife springt zu Next RG und die RG.Address waere dann $A$11. Da aber die Zeile 10 gel;oescht wurde muss ich wieder bei Adresse $A$10 beginnen und nicht bei $A$11 da ich sonst eine Zeile ausgelassen habe. Ich muss demnach der RG-Variablen sagen dass sie bei einem anderen RG.Address weitermachen soll, also bei $A$10 und nicht bei $A$11. Wie weisse ich der RG-Variablen anstelle der Adresse die ihr durch die Schleife zugewiesen wurde die Adresse zu bei der sie weitermachen soll ?
Gruss
Roland

Anzeige
AW: Zeilen löschen: Duplikate entfernen
23.06.2015 10:34:49
Daniel
Hi
kleiner Tip:
verwende zum Löschen von Zeilen mit Bediungung das Duplikate-Entfernen aus den Daten-Datentools.
das ist deutlich schneller, als jede Zeile einzeln zu prüfen und zu löschen.
das geht im Prinzip so:
1. Schreibe in eine Hilfsspalte am Tabellenende eine Formel, welche alle Zeilen, die gelöscht werden sollen, mit 0 markiert und alle die stehenen bleiben müssen mit der Zeilennummer.
das wäre in deinem Fall: =Wenn(Oder(Links(G1;1)="A";Links(H1;1)="A");0;Zeile())
2. Schreibe in die Überschriftenzeile ebenfalls die 0
3. wende auf die Tabelle die Funktion DATEN - DATENTOOLS - DUPLIKATE ENTFERNEN an, mit der Hilfsspalte als Kriterium und der Option "keine überschrift"
4. Lösche die Hilfsspalte.
die funkition löscht dann alle Zeilen mit 0, ausser der der ersten und das ist die Überschrift, die sowieso stehen bleiben soll
sieht als Makro so aus:

With ActiveSheet.usedRange
With .Columns(.columns.count + 1)
.FormulaR1C1 = "=IF(OR(Left(RC7,1)=""A"",Left(RC8,1)=""A""),0,Row())"
.Cells(1, 1).Value = 0
.EntireRow.RemoveDuplicates .Column, xlno
.clearcontents
end With
End With
das ist nicht nur schneller als eine Schleife, es ist auch in der Testphase des Programms wesentlich angenehmer, denn wenn du im Einzelstep testest, musst du dich nicht durch die Schleife durchklicken oder mit Haltepunkten rumhantieren, sondern du klickst dich einfach durch diese 4 Schritte durch.
ausserdem werden alle Zeilen vor dem Löschen markiert, dh du kannst, bevor du endgültig löschst, einfach prüfen, ob auch die richtigen markiert wurden.
Gruß Daniel

Anzeige
AW: Zeilen löschen: Duplikate entfernen
23.06.2015 11:14:39
Roland
Hallo und vielen Dank fuer Eure Hilfe,
habe verstanden, dass ich der RG-Variabelen keinen anderen Range-Wert zuweisen kann. Trotzdem vielen Dank fuer Eure Anregungen.
Habe in der Zwischenzeit bereits zwei Schleifen geschrieben. Eine fuer die Zeilen zu loeschen und eine fuer alle zellen zu bearbeiten. Ich muss leider alle zellen durchlaufen weil ich alle Zellen noch bearbeiten muss (ausser die letzten beiden Spalten und die letzten beiden Spalten pruefe ich auf Werte um die Zeilen zu loeschen).
Vielleicht werde ich die Idee von Daniel noch aufgreifen.
Also nochmals vielen Dank fuer Eure Hilfe
Roland

Anzeige
AW: Zeilen löschen: Duplikate entfernen
23.06.2015 14:51:25
Roland
Noch eine Frage an Daniel,
kannst Du mir die eine Zeile
.EntireRow.RemoveDuplicates .Column, xlno
naeher erklaeren bitte.
Der erste Teil ".EntireRow.RemoveDuplicates" ist mir klar.
Aber dann kommt mit etwas Abstand ".column, xlno" und das verstehe ich nicht
Danke Roland

AW: Zeilen löschen: Duplikate entfernen
23.06.2015 16:05:56
Daniel
Hi
wenn du das Duplikate-Entfernen ausführst (dh von Hand, nicht mit Excel) musst du zusätzlich zum Zellbereich zwei weitere Daten angeben:
1. die Spalte(n), welche für die Duplikatsprüfung verwendet werden
2. ob in der angegebenen Tabelle eine Überschriftenzeile vorhanden ist oder nicht.
diese beiden Informationen müssen natürlich auch in einen Makro gegeben werden und das passiert hier.
vollständigkeitshalber kann man die Parameter noch benennen:
.RemoveDuplicates Columns:=.Column, Header:=xlNo
aber dieses Benennung der Parameter kann man weglassen, wenn man die Parameter in der richtigen Reihenfolge hinschreibt.
Gruß Daniel

Anzeige
AW: Zeilen löschen: Duplikate entfernen
23.06.2015 16:21:07
Roland
Danke Daniel,
hast Du vielleicht so eine clevere loesung fuer das entfernen von Leerzeichen vor und nach einem Wert ohne verwenden einer Schleife?
Also z.B. Spalte A bis H alle Zellen bearbeiten mit
Zelle = Trim(Zelle)
aber die nachfolgenden Spalten nicht.
Ware super.
Danke
Roland

AW: Zeilen löschen: Duplikate entfernen
23.06.2015 16:38:44
Daniel
Hi
so vielleicht:
with activesheet.Usedrange
With .columns(.columns.Count + 1).Resize(, 8)
.formular1c1 = "=Trim(RC[-" & .column - 1 & "])
.copy
.cells(1, 1).pastespecial xlpastevalues
.clearcontents
end with
end with

das fügt dann am Tabellenende die Formel ein =Glätten(A1), halt 8 Spalten breit und soviel Zeilen wie vorhanden.
Dann werden die Formelergebnisse als Wert nach A1 zurückkopiert und die Formel wird wieder gelöscht.
alternativ auch per Schleife, das ist ähnlich schnell, wenn man die Werte in ein Array kopiert und dort bearbeitet:

dim arr
dim z as long, s as long
with ActiveSheet.Usedrange.columns(1).Resize(, 8)
arr = .value
for z = 1 to ubound(arr, 1)
for s = 1 to ubound(arr, 2)
arr(z, s) = Trim(arr(z, s))
Next s
next z
.value = arr
end with

beachte, dass das VBA-Trim etwas anders funktioniert als die Glätten-Funktion in Excel.
die Glätten-funktion löscht nicht nur die Leerzeichen am Anfang und am Ende, die reduziert auch alle mehrfach-Leerzeichen innerhalb des Textes auf ein Leerzeichen.
Gruß Daniel

Anzeige
AW: Zeilen löschen: Duplikate entfernen
24.06.2015 06:37:35
Roland
Danke Daniel fuer Deine Hilfe.
Ich habe gestern Abend eine andere Loesung gefunden fuer Trim().
dim RG as Range
Set RG = ActiveSheet.Range("A1:F" & ActiveSheet.UsedRange.Rows.Count)
RG.Value = Application.TRIM(RG.Value)
klappt super und ist sehr schnell.
Schoenen tag noch
Roland

die andere Loesung gefunden fuer Trim ist falsch!
24.06.2015 07:55:11
Matthias
Hallo
klappt super und ist sehr schnell
Ist aber falsch.
Nimm mal eine neue Mappe
Schreibe nun nur in C4 und D5 irgend einen Text mit ein paar Leerzeichen.
Lass nun mal den Code laufen:
Dim RG As Range
MsgBox "UsedRange.Address: " & ActiveSheet.UsedRange.Address & vbLf & "letzte Zeile im UsedRange: " & ActiveSheet.UsedRange.Rows.Count, vbInformation
Set RG = ActiveSheet.Range("A1:F" & ActiveSheet.UsedRange.Rows.Count)
RG.Value = Application.Trim(RG.Value)
Den blauen Text bitte in eine Zeile im Code schreiben.
Vor und nach dem &-Zeichen bitte immer ein Leerzeichen im Code benutzen.
Die MsgBox soll nur zum Verstehen dienen.
Du würdest also hier nur in A1:F2 die Leerzeichen entfernen!
Da steht aber nichts!
Sollte das bei Dir nicht klappen hier die Bsp-Datei
https://www.herber.de/bbs/user/98419.xlsm
Gruß Matthias

Anzeige
AW: die andere Loesung gefunden fuer Trim ist falsch!
24.06.2015 09:39:13
Daniel
Hi Matthias
hier gings ja zunächst mal um die Trim-Funktion, und die klappt super.
das kannte ich auch noch nicht, zumal das Trim nach Application gar nicht in der Intellisense angezeigt wird.
das die Zuweisung des Zellbereichs nur für den Fall passt, dass die Usedrange in A1 beginnt, ist in diesem Thread doch eher nensächlich.
das kann man dann auch einfacher gestalten:
set RG = ActiveSheet.Usedragen.Columns(1).Resize(, 6)
oder
set RG = Intersect(Activesheet.UsedRange, ActiveSheet.Range("A:F"))
Gruß Daniel

Application.Trim ist Worksheet.Function.Trim ...
24.06.2015 14:57:22
Luc:-?
…also die XlStandardFkt GLÄTTEN, die sich deutlich anders verhält als die vbFkt Trim, weshalb sie wohl auch im WorksheetFunction-Container-Objekt enthalten ist, Daniel;
auf diese Fktt reagiert der VBE-Intellisense nicht und in der Auswahl­Anzeige gehen sie in der Fülle der Angebote für Application.… unter.
Gruß, Luc :-?

Anzeige
AW: Application.Trim ist Worksheet.Function.Trim ...
24.06.2015 20:01:23
Roland
Also vielen Dank Euch allen fuer die vielen Info's
Alles habe ich noch nicht verstanden (liegt unter Anderem auch an den vielen Abkuerzungen die Ihr benutzt). Muss ich mir bei Gelegenheit nochmals naeher anschauen.
Meine Loesung klappt jedenfalls super fuer das was ich in diesem Fall benoetige.
Also nochmals vielen Dank.
Roland

AW: Application.Trim ist Worksheet.Function.Trim ...
25.06.2015 10:36:19
Daniel
... Sicher Luc?
ich dachte immer, dass die Funktionen aus Application andere sind als die aus Worksheetfunction.
beispielsweise erzeugt
erg = WorksheetFunction.Match("xxx", Range("a:a"), 0)

einen Fehlerabbruch, wenn "xxx" nicht im Suchbereich vorhanden ist,
während
erg = Application.Match("xxx", Range("a:a"), 0)

fehlerfrei durchläuft und der Variable erg dann eben den Fehlerwert zuweist.
(erg ist in beiden Fällen als Variant deklariert)
in sofern hatte ich eigentlich gedacht, dass Application und Worksheetfunction unterschiedliche "Container" sein müssen.
Gruß Daniel

Anzeige
Das ist der einzige Unterschied, ...
25.06.2015 13:27:11
Luc:-?
…Daniel,
weil Application der „große Container“ für alles ist und die Fkt darin keiner „Nachbehandlung“ unterzogen wird, was aber bei WorksheetFunction-Verwendung passiert, wodurch kein Fehlerwert durchgelassen, sondern die Operation einfach abgebrochen wird. Das kannst du ja mal mit einer eigenen UDF, die du in ein Klassenmodul steckst, testen.
Ich gehe also davon aus, dass es sich nur um 2 verschiedene VBA-Zugriffsmethoden auf die gleiche Fkt handelt, wobei der „Container im Container“ quasi ergebnis-normierend wirkt (dafür findet man dort per VBE-Intellisense die im direkten VBA-Zugriff stehenden Xl-Fktt leichter). Es handelt sich also wohl kaum um eine 2-Fach-Pgmierung für VBA, wie verschiedentlich vermutet wird (warum sollte MS das auch tun?!).
Luc :-?

AW: Das ist der einzige Unterschied, ...
25.06.2015 14:01:02
Daniel
Hi
vermutest du jetzt auch nur oder weisst du was gesichert?
was ist der unterschied zwischen einer UDF in einem Klassenmodul und einem Allgemeinen Modul?
warum muss es eine UDF in einem Klassenmodul sein und nicht in einem allgemeinen Modul wie sonst üblich für eine UDF?
Gruß Daniel

Weil das bei WorksheetFunction mit Sicherheit ...
25.06.2015 14:10:43
Luc:-?
…so ist, Daniel;
warum wohl kann man sowohl Application als auch WorksheetFunction den FktsNamen nach Pkt nachstellen…? ;->
Mit einem (bereits vorhandenen) Klassenmodul lässt sich das leichter und schneller testen, als wenn du erst ein AddIn anlegen wolltest, dessen VBAProjekt du dann auch einen eigenen, speziellen CodeNamen geben müsstest…
Luc :-?

Das geht so nicht, ...
23.06.2015 10:43:06
Luc:-?
…Roland,
das haben Matti & Werner versucht, dir zu erklären! Anderenfalls, also mit deinem Schleifentyp, müsstest du das Pgm mehrfach laufen lassen.
Gruß, Luc :-?
Besser informiert mit …

AW: Erst alle sammeln, dann gemeinsam löschen
23.06.2015 10:51:17
Daniel
Hi
wenn du unbedingt mit der For-Each-Schleife arbeiten willst, könntest du auch zuerst die zu löschenden Zeilen in einer weitern Range-Variable sammeln und dann am Schluss löschen.
Dann hast du das Problem mit dem Überspringen nicht.
ausserdem ist deine Schleife überflüssig lang.
es reicht, wenn die Schleife pro Zeile eine Zelle durchläuft und nicht 6 (A:F):

dim rngLösch as Range
dim RG as Range
For Each RG In ActiveSheet.Range("A2:A" & ActiveSheet.UsedRange.Rows.Count)
If Right(Range("G" & RG.Row), 1) = "A" Or Right(Range("H" & RG.Row), 1) = "A" Then
if rngLösch is nothing then
set rngLösch = RG
Else
set rngLösch = Union(rngLösch, RG)
end If
Else
anderer Code
End If
Next
rngLösch.entireRow.Delete
Gruß Daniel

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige