Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
936to940
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
936to940
936to940
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

letzte Zeile mit Rekursion

letzte Zeile mit Rekursion
04.01.2008 17:02:00
Jakob
Hallo allerseits,
ich habe die Aufgabe, die Zeile des letzten (untersten) Eintrags in einer Tabelle zu ermitteln. Dies soll mit einer rekursiven Funktion in VBA passieren. Das Prinzip ist mir eigentlich klar:
Teile die Tabelle in der Hälfte und wenn du in der einen Hälfte was findest, dann teile den Bereich wieder.
Ich habe auch ein Makro (zugegeben nicht von mir...), dass funktioniert, aber ... ich verstehe es absolut nicht.
meine Fragen:
Kann mir jemand erklären, was da passiert?
Warum ist zum Schluss LastRow = lngFirstRow?
Kann ich diese "Function" auch in einer Tabelle als Funktion nutzen? wenn ja, wie?
Habt ihr vielleicht andere Vorschläge?
vielen Dank für eure Hilfe
Jakob
PS: ich habe das Makro von einem Bekannten, der es aber auch nicht geschrieben hat. Christoph M kennen wir beide nicht. Vielleicht kennt ihr ihn ja.

Sub test()
MsgBox LastRow(Sheets("Tabelle1"))
End Sub



Function LastRow(wks As Worksheet, Optional lngFirstRow&, Optional lngLastRow&) As Long
'Christoph M.
Dim lngTmp&
With Application
If .CountA(wks.Rows(wks.Rows.Count)) Then
LastRow = wks.Rows.Count: Exit Function
End If
If lngFirstRow = 0 Then lngFirstRow = 1
If lngLastRow = 0 Then lngLastRow = wks.Rows.Count
lngTmp = ((lngFirstRow + lngLastRow) / 2) \ 1
If lngLastRow > lngFirstRow + 1 Then
If .CountA(wks.Rows(lngTmp & ":" & lngLastRow)) Then lngFirstRow = lngTmp
If .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) Then lngLastRow = lngTmp
LastRow wks, lngFirstRow, lngLastRow
End If
End With
LastRow = lngFirstRow
End Function


13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 18:50:00
Uduuh
Hallo,
die Funktion liefert falsch Werte.
Korrektur:

Function LastRow(wks As Worksheet, Optional lngFirstRow&, Optional lngLastRow&) As Long
'Christoph M., Korrektur: Uduuh
Dim lngTmp&, blnFound As Boolean
With Application
If .CountA(wks.Rows(wks.Rows.Count)) Then
LastRow = wks.Rows.Count: Exit Function
End If
If lngFirstRow = 0 Then lngFirstRow = 1
If lngLastRow = 0 Then lngLastRow = wks.Rows.Count
lngTmp = ((lngFirstRow + lngLastRow) / 2) \ 1
If lngLastRow > lngFirstRow + 1 Then
If .CountA(wks.Rows(lngTmp & ":" & lngLastRow)) Then lngFirstRow = lngTmp: blnFound =  _
True
If Not blnFound Then
If .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) Then lngLastRow = lngTmp
End If
LastRow wks, lngFirstRow, lngLastRow
End If
End With
LastRow = lngFirstRow
End Function


Gruß aus’m Pott
Udo

Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 19:05:00
Christoph
hallo Jakob,
der Code von dir ist falsch. Er stammt zwar ursprünglich von mir, wurde aber wohl in der Zwischenzeit verändert. Mein Code von 11/2005 siehe unten.
Gruß Christoph M.
PS: nach dem letzten Post von Uduuh, werde ich meinen aber auch noch mal näher anschauen...

Function LastRow(wks As Worksheet, Optional lngFirstRow&, Optional lngLastRow&) As Long
'Christoph M. 11/2005
Dim lngTmp&
With Application
If .CountA(wks.Rows(wks.Rows.Count)) Then
LastRow = wks.Rows.Count: Exit Function
End If
If lngFirstRow = 0 Then lngFirstRow = 1
If lngLastRow = 0 Then lngLastRow = wks.Rows.Count
lngTmp = ((lngFirstRow + lngLastRow) / 2) \ 1
If lngLastRow > lngFirstRow + 1 Then
If .CountA(wks.Rows(lngTmp & ":" & lngLastRow)) Then
lngFirstRow = lngTmp
ElseIf .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) Then
lngLastRow = lngTmp
End If
LastRow wks, lngFirstRow, lngLastRow
End If
End With
LastRow = lngFirstRow
End Function


Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 19:15:00
Jakob
Hallo Christoph und auch Uduuh
die Korrektur verstehe ich nicht so ganz, was ist da der Unterschied?

AW: letzte Zeile mit Rekursion
04.01.2008 19:20:30
Christoph
Jakob,
du fragst innerhalb einem Durchgang zwei mal mit:
If .CountA(wks.Rows(lngTmp & ":" & lngLastRow)) Then lngFirstRow = lngTmp
und
If .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) Then lngLastRow = lngTmp
bei meinem Code und auch bei Uduuh's schließt die ein Abfrage die nächste aus.
Auf den Boolean von Uduuh kann man aber verzichten, wenn du meinem ursprünglichen Code bleibst.
(@Uduuh: korrigiere mich bitte, wenn ich falsch liege)
Gruß
Christoph

Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 19:20:00
Christoph
Jakob,
du fragst innerhalb einem Durchgang zwei mal mit:
If .CountA(wks.Rows(lngTmp & ":" & lngLastRow)) Then lngFirstRow = lngTmp
und
If .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) Then lngLastRow = lngTmp
bei meinem Code und auch bei Uduuh's schließt die ein Abfrage die nächste aus.
Auf den Boolean von Uduuh kann man aber verzichten, wenn du meinem ursprünglichen Code bleibst.
(@Uduuh: korrigiere mich bitte, wenn ich falsch liege)
Gruß
Christoph

AW: letzte Zeile mit Rekursion
04.01.2008 20:32:00
Jakob
hallo Christoph, Uduuh, Daniel,
wenn ich das richtig sehe, ist bei Daniels Korrektur noch dieser Fehler enthalten.
Außerdem ist bei ihm keine Rekursion enthalten?
Den Vorteil von "mit Rekursion" oder "ohne Rekursion" ist mir noch nicht ganz klar.
Die Aufgabe in unsererem Kurs lautete "mit Rekursion".
Dann werde ich mich mal weiter auf den Code von Christoph konzentrieren.
Vielen Dank an alle
Jakob

Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 20:49:00
Christoph
Hallo Jakob,
Die Funktion mit Rekursion ruft die Funktion mehrfach auf und muss nach dem Finden des Ergebnisses auch eben so oft beendet werden. Daher ist der Code ohne Rekursion eleganter. Der Code war damals ein Beispiel für Rekursion.
Du findest im Web x Beispiele zu "RealLastCell". Diese arbeiten aber alle mit "UsedRange" bzw "xlLastCell" von Excel.
Wenn du in einer Tabelle den letzten Eintrag zB in Zeile 30 hast, aber ein einzelne Zelle in Zeile 60.000 formatiert ist (Rahmen, Farbe, etc.), dann braucht die "RealLastCell"-Funktion viel länger, da sie von 60.000 bis 30 in einer For-Schleife die einzelnen Zeilen abarbeitet.
Der Vorteil der Funktion "LastRow" (ob jetzt mit Rekursion oder ohne nach dem Ansatz von Daniel) ist, das bei 65536 Zeilen nur 15 Durchläufe benötigt werden, um die echte letzte Zeile mit einem Eintrag zu finden. Einen Nachteil gegenüber "RealLastCell" besteht nur dann, wenn der "UsedRange" und die letzte Zeile mit einem Eintrag sich um weniger als 15 Zeilen unterscheidet. Ich würde also immer "LastRow" bevorzugen. Beide funktionieren auch bei mit dem Autofilter gefilterten Tabellen.
Gruß
Christoph

Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 21:01:00
Daniel
Soso, du lässt dir hier also Kursaufgaben lösen du Schlingel ;-)
das ist BöseBöseBöse
ich sehe für diese Aufgabenstellung in der Rekursion keinen Vorteil, wenn man es genauso mit der Do-Schleife lösen kann.
im Prinzip wird die Lösung durch die Do-Schleife ja einfacher, weil die die beiden IF-Abfragen entfallen können und auch der Programmteil, in dem die letzte Zeile geprüft wird, nur einmal und nicht überflüssigerweise mehrfach durchlaufen wird.
Aber du kannst euren Ausbilder mal dazu befragen und dann seine Antwort hier Posten, das würde mich auch interessieren.
Die Korrektur kannst du ja in meine Lösung noch einbauen.
Gruß, Daniel

Anzeige
@Daniel
04.01.2008 23:52:00
Christoph
Hallo Daniel,
ich weiß, das Thema ist reichlich ausgelutscht, aber vielleicht ist der Ansatz ja doch noch nicht so geläufig? (mit Excel und VBA hab ich in letzter Zeit nicht all zu oft zu tun).
Die Funktion ohne Rekursion auzuführen hat natürlich Vorteile - siehe unsere Antworten an Jakob.
Jakob hatte nach 'ner Lösung mit Rekursion gefragt - warum auch immer, es übt auf jeden Fall.
Ich nutze udf's eigentlich ausschließlich in VBA, daher hier ein Mix aus unseren beiden Codes.
Was hälst du davon? Wie schon geschrieben sehe ich hier Vorteile gegenüber "RealLastCell" von John Walkenbach.
Grüße
Christoph

Function LastRow(wks As Worksheet) As Long
'Christoph M. / Daniel
'Zeile des letzte Eintrages in einer Tabelle (0 bei keinem Eintrag)
Dim lngFirstRow&, lngLastRow&, lngTmp&
With Application
If .CountA(wks.Cells) = 0 Then Exit Function
If .CountA(wks.Rows(wks.Rows.Count)) Then
LastRow = wks.Rows.Count: Exit Function
End If
lngFirstRow = 1
lngLastRow = wks.Rows.Count
Do While lngLastRow > lngFirstRow + 1
lngTmp = ((lngFirstRow + lngLastRow) / 2) \ 1
If .CountA(wks.Rows(lngTmp & ":" & lngLastRow)) Then
lngFirstRow = lngTmp
ElseIf .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) Then
lngLastRow = lngTmp
End If
Loop
End With
LastRow = lngFirstRow
End Function


Anzeige
AW: @Daniel
05.01.2008 00:15:44
Daniel
Hi
ich kenne jetzt die RealLastCell-Funktion nicht, um das beurteilen zu können.
Deine Rekursiv-Methode ist schon sehr elegant und mit ihren 16 Berechnungsläufen sehr effizient.
durch die Do-Schleife bleibt das Prinzip der Rekursion ja erhalten (die Berechnungsergebnise eines Durchlaufs werden im Folgedurchlauf als Ausgangsgrösse verwendet), auch wenn sich die Funktion nicht selbst aufruft.
Daher denke ich, daß Jakob auch die Variante mit der Do-Schleife als Lösung verwenden kann, um die von dir genannten Nachteile zu vermeiden.
Wenn ich selbst so ne Funktion hätte schreiben müssen, hätte ich wahrscheinlich die Spalten der UsedRange mit .End(xlUp) geprüft und von diesen Werten das Maximum genommen.
Aber auch hier wären bis zu 256 Berechnungen erforderlich (und hätte bei gefilterten Tabellen nicht funktioniert)
Gruß, Daniel

Anzeige
AW: @Daniel
05.01.2008 00:32:00
Christoph
ok, dann lassen wir das so stehen... kann man bestimmt noch mal einsetzen.
Grüße
Christoph

letzte Zeile / letzte Spalte
05.01.2008 12:49:00
Christian
Hallo,
Jungs, das Prinzip ist klasse! Noch eine kleine Vereinfachung - auf die 2. Abfrage mit "ElseIf" kann man verzichten, da ein Eintrag in der ersten Hälfte sein muss, wenn er in der zweiten Hälfte nicht gefunden wurde. Also reicht hier ein einfaches "Else". Die Variablennamen hab ich noch angepasst, da der Code ebenso auf Spalten angewendet werden kann.
schönes WE, Christian

Function LastRow(wks As Worksheet) As Long
'letzte Zeile mit Eintrag (Prinzip von Christoph M.)
Dim lngFirst&, lngLast&, lngTmp&
With wks
If Application.CountA(.Cells) = 0 Then Exit Function
If Application.CountA(.Rows(.Rows.Count)) Then
LastRow = .Rows.Count: Exit Function
End If
lngLast = .Rows.Count
Do While lngLast > lngFirst + 1
lngTmp = ((lngFirst + lngLast) / 2) \ 1
If Application.CountA(.Range(.Rows(lngTmp), .Rows(lngLast))) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
End With
LastRow = lngFirst
End Function



Function LastCol(wks As Worksheet) As Long
'letzte Spalte mit Eintrag (Prinzip von Christoph M.)
Dim lngFirst&, lngLast&, lngTmp&
With wks
If Application.CountA(.Cells) = 0 Then Exit Function
If Application.CountA(.Columns(.Columns.Count)) Then
LastCol = .Columns.Count: Exit Function
End If
lngLast = .Columns.Count
Do While lngLast > lngFirst + 1
lngTmp = ((lngFirst + lngLast) / 2) \ 1
If Application.CountA(.Range(.Columns(lngTmp), .Columns(lngLast))) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
End With
LastCol = lngFirst
End Function


Anzeige
AW: letzte Zeile mit Rekursion
04.01.2008 19:07:00
Daniel
Hi
mal zur ersten Frage:
das Funktionsprinzip ist halt, so, daß der Zellbereich, mit dem Gearbeitet wird, immer wieder halbiert wird.
enthält der untere Bereich keine Werte mehr wird mit dem oberen weitergearbeitet, ansontsten mit dem unteren.
Das wird solange wiederholt, bis nur noch ein 2-Zeiliger Zellbereich übrig bleibt, von dem die untere Zeile leer ist und die Obere den letzten Wert enthält.
deswegen ist lngFirsRow das korrekte ergebnis.
Wenn du diese Funktion als normale Excelfunktion nutzen willst, dann musst du sie etwas umschreiben, weil du in einer Excel-Funktion kein Sheet als Parameter übergeben kannst, sondern nur Zellen, dh. du musst aus der Zelle das Worksheet ableiten, das geht mit der Funktion Range(xxx).Parent
So könnte es funktionieren.

Function LastRow2(xxx As Range) As Long
'Christoph M. gändert von Daniel
Dim lngTmp&, lngFirstRow&, lngLastRow&
Dim wks As Worksheet
Set wks = xxx.Parent
With Application
If .CountA(wks.Rows(wks.Rows.Count)) Then
LastRow2 = wks.Rows.Count: Exit Function
End If
If .CountA(wks.Cells) = 0 Then
LastRow2 = 0: Exit Function
End If
lngFirstRow = 1
lngLastRow = wks.Rows.Count
Do
lngTmp = ((lngFirstRow + lngLastRow) / 2) \ 1
If lngLastRow  0 Then lngFirstRow = lngTmp
If .CountA(wks.Rows(lngFirstRow & ":" & lngTmp)) > 0 Then lngLastRow = lngTmp
Loop
End With
LastRow2 = lngFirstRow
End Function


ich hab allerding des Rekrusiv-Aufruf in eine Do-Schleife umgewandelt, das scheint mir an dieser Stelle sinnvoller und vereinfacht die Funktion sogar.
als Parameter musst du jetzt eine Range übergeben (also einen Zellebreich) und nicht ein Worksheet, damit funtktioniert die Funktion auch als Excel-Funktion (mit einem beliebigen Bezug im gewünschten Blatt als Parameter:)
=LastRow2(A1)
Gruß, Daniel
PS. die Funktion einfach ins allgemeine Modul kopieren, dann kannst du sie normal in Excel verwenden.

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige