Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender Navigationstipps
VBA: Letzte Zeile ermitteln
23.04.2009 10:15:59
Boris
Hi,
ich würde gerne die letzte beschriebene Zeile (= mindestens eine beschriebene Zelle in einer beliebigen Spalte) eines Blattes ermitteln unter Ignorierung eines eventuell gesetzten Autofilters.
Mit Worksheets(1).UsedRange.Address erhalte ich den gesamten beschriebenen Bereich (z.B. $A$1:$V$45)
Wie kann ich nun die 45 aus diesem String herausfiltern?
Oder gibt es noch andere Methoden um dieses Problem zu lösen?
PS: Funktionen wie Cells.SpecialCells(xlCellTypeLastCell).Address oder Cells(Rows.Count, 1).End(xlUp).Row liefern als Ergebnis die letzte gefilterte Zeile...
Grüße, Boris

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA: Letzte Zeile ermitteln
23.04.2009 10:25:25
Günther
das geht per VBA ganz einfach.
'letzte Zeile in Spalte A suchen:
Zeilenanzahl = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'letzte Spalte in Zeile 1 suchen
Spaltenanzahl = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
AW: VBA: Letzte Zeile ermitteln
23.04.2009 11:45:39
Boris
Dies funktioniert aber wie in "PS: ..." dargestellt nicht bei gefilterten Tabellen...
AW: VBA: Letzte Zeile ermitteln
23.04.2009 12:52:49
Rudi
Hallo,

With Worksheets(1).UsedRange
leZeile=.Row + .Rows.Count -1
end with


UsedRange verwende ich allerdings nie.
Gruß
Rudi

AW: VBA: Letzte Zeile ermitteln
23.04.2009 20:12:40
Christian
Hallo,
noch 'ne Variante - da ich dem "UsedRange" nicht so recht traue, verwende ich kein "UsedRange".
Dieser Code funktioniert auch bei Autofilter.
Gruß
Christian

Option Explicit
Sub test()
MsgBox "LastRow: " & LastRow(Sheets("Tabelle1"))
End Sub
Function LastRow(wks As Worksheet) As Long
Dim lngFirst As Long, lngLast As Long, lngTmp As Long
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
lngLast = wks.Rows.Count
Do While lngLast > lngFirst + 1
lngTmp = (lngFirst + lngLast) \ 2
If .CountA(wks.Rows(lngTmp).Resize(lngLast - lngTmp)) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
If .CountA(wks.Rows(lngLast)) Then LastRow = lngLast Else LastRow = lngFirst
End With
End Function


Anzeige
AW: VBA: Letzte Zeile ermitteln
24.04.2009 09:03:03
Boris
Funktioniert einwandfrei, verstehe allerdings nicht genau, warum sich die Funktion von unten annähert und den Wert halbiert und sich dann bei Unterschreiten der Zielgröße wieder nach oben bewegt?
Der Vorteil dieser Funktion ist auch, dass er die letzte beschriebene Zeile ermittelt und nicht wie bei UsedRange die letzte manipulierte (z.B. eingefärbte) Zeile.
Super, danke, Boris
PS: Funktioniert das auch für Spalten, wenn ich sämtliche "Rows" durch "Columns" ersetze?
AW: VBA: Letzte Zeile ermitteln
24.04.2009 10:32:32
Christian
Hallo Boris,
wie die Fnct arbeitet...
- suche in unteren Hälfte nach Eintrag.
- wenn da nichts drinsteht, dann muss der letzte Eintrag in der oberen Hälfte sein.
- Dann halbiere diesen Bereich und suche hier in der unteren Hälfte nach einem Eintrag usw. bis nur noch eine Zeile übrig bleibt.
Die Funktion benötigt bei 65536 (=2^16) Zeilen nur 16 Schritte, um die letzte Zeile mit Eintrag zu ermitteln.
Für Spalten funktioniert das genauso, bei dem Resize musst du aufpassen ... siehe Code.
Gruß
Christian

Function LastCol(wks As Worksheet) As Long
Dim lngFirst As Long, lngLast As Long, lngTmp As Long
With Application
If .CountA(wks.Cells) = 0 Then Exit Function
If .CountA(wks.Columns(wks.Columns.Count)) Then
LastCol = wks.Columns.Count: Exit Function
End If
lngLast = wks.Columns.Count
Do While lngLast > lngFirst + 1
lngTmp = (lngFirst + lngLast) \ 2
If .CountA(wks.Columns(lngTmp).Resize(, lngLast - lngTmp)) Then _
lngFirst = lngTmp Else lngLast = lngTmp
Loop
If .CountA(wks.Columns(lngLast)) Then LastCol = lngLast Else LastCol = lngFirst
End With
End Function


Anzeige
AW: VBA: Letzte Zeile ermitteln
24.04.2009 10:38:03
Boris
Sensationell, nach einer derartigen Funktionen habe ich immer gesucht, v.a. dass diese Formatierungen nicht berücksichtigen hilft mir sehr weiter...
Vielen Dank

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige
Anzeige

Infobox zum Thema

EXCEL - VBA-Funktion zur Ermittlung der letzten gefüllten Zeile


Inhaltsverzeichnis

  1. Problemstellung
  2. Benutzerdefinierte VBA-Funktion
  3. Schritt-für-Schritt-Anleitung
  4. Anwendungsbeispiele aus der Praxis
  5. Tipps
  6. Zusammenfassung

Problemstellung

Du möchtest die letzte beschriebene Zeile in einem Excel-Arbeitsblatt ermitteln. Zusätzlich soll die Funktion mit Autofiltern kompatibel sein und Spalten unterschiedlicher Länge berücksichtigen können.

Standardmäßige Excel-Funktionen bieten keine Möglichkeit, alle diese Anforderungen in einer einzigen Funktion zu erfüllen. Daher ist eine benutzerdefinierte VBA-Funktion hier der beste Ansatz.


Benutzerdefinierte VBA-Funktion

Mit VBA (Visual Basic for Applications) kannst du eine benutzerdefinierte Funktion erstellen, die alle diese Bedingungen berücksichtigt.

Das Ziel ist, durch alle Spalten und Zeilen zu iterieren und dabei Autofilter und unterschiedliche Spaltenlängen zu berücksichtigen. Wir können dies mit einer VBA-Funktion realisieren.


Schritt-für-Schritt-Anleitung

  1. Öffne den VBA-Editor, indem du Alt + F11 drückst.
  2. Gehe zu Einfügen > Modul, um ein neues Modul hinzuzufügen.
  3. Kopiere und füge den folgenden VBA-Code in das Modul ein.
Function GetLastRow(wsName As String) As Long
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim col As Long
    Dim tmpLastRow As Long

    Set ws = Worksheets(wsName)
    lastRow = 1

    For col = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
        tmpLastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
        If tmpLastRow > lastRow Then lastRow = tmpLastRow
    Next col

    GetLastRow = lastRow
End Function
  1. Speichere das Modul und schließe den VBA-Editor.

Nun kannst du die Funktion GetLastRow in deinem Excel-Arbeitsblatt verwenden, indem du =GetLastRow("Blattname") eingibst.


Anwendungsbeispiele aus der Praxis

  • Datenanalyse: Erkenne, wie viele Datensätze in deinem Arbeitsblatt vorhanden sind.
  • Automatisierung von Aufgaben: Verwende die Funktion in Kombination mit anderen VBA-Codes, um Aktionen wie Sortieren oder Filtern automatisch auf alle vorhandenen Daten anzuwenden.

Tipps

  • Wenn du die Funktion in mehreren Arbeitsmappen verwenden möchtest, überlege, sie in einer persönlichen Makroarbeitsmappe zu speichern.

Zusammenfassung

Die benutzerdefinierte VBA-Funktion GetLastRow ermöglicht es dir, die letzte beschriebene Zeile in einem Excel-Arbeitsblatt zu finden. Sie ist mit Autofiltern kompatibel und berücksichtigt Spalten unterschiedlicher Länge. Diese Funktion ist besonders nützlich für die Automatisierung von Aufgaben und die Datenanalyse.

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige