Letzte Zeile und letzte Spalte per VBA ermitteln

Ein Makro soll neue Daten unter eine Liste schreiben, alle Datensätze durchlaufen oder einen Bereich kopieren. Dafür muss es wissen, wo die Daten enden. Excel bietet dafür mehrere Wege, die sich in Sonderfällen deutlich unterscheiden.

Die Kurzlösung

In den meisten Fällen genügt diese Zeile. Sie liefert die letzte belegte Zeile in Spalte A:

letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Das Makro springt dabei in die unterste Zelle der Spalte und von dort nach oben bis zur ersten belegten Zelle, so wie die Tastenkombination Strg+Pfeil nach oben. Lücken in der Liste stören nicht. ws.Rows.Count liefert die Zeilenzahl des Blatts, die Zeile funktioniert deshalb in jeder Excel-Version.

Fertige Funktionen zum Kopieren

Die folgenden Funktionen decken die üblichen Fälle ab. Fügen Sie sie in ein Standardmodul ein (im VBA-Editor über Einfügen, Modul).

Option Explicit

'Letzte belegte Zeile einer Spalte (Spalte als Buchstabe oder Nummer)
Public Function LetzteZeile(ByVal ws As Worksheet, Optional ByVal Spalte As Variant = 1) As Long
    LetzteZeile = ws.Cells(ws.Rows.Count, Spalte).End(xlUp).Row
End Function

'Letzte belegte Spalte einer Zeile
Public Function LetzteSpalte(ByVal ws As Worksheet, Optional ByVal Zeile As Long = 1) As Long
    LetzteSpalte = ws.Cells(Zeile, ws.Columns.Count).End(xlToLeft).Column
End Function

'Letzte Zeile mit Inhalt im ganzen Blatt, 0 bei leerem Blatt
Public Function LetzteZeileBlatt(ByVal ws As Worksheet) As Long
    Dim fund As Range
    Set fund = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
                             LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not fund Is Nothing Then LetzteZeileBlatt = fund.Row
End Function

'Letzte Spalte mit Inhalt im ganzen Blatt, 0 bei leerem Blatt
Public Function LetzteSpalteBlatt(ByVal ws As Worksheet) As Long
    Dim fund As Range
    Set fund = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
                             LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If Not fund Is Nothing Then LetzteSpalteBlatt = fund.Column
End Function

So verwenden Sie die Funktionen, zum Beispiel um einen neuen Eintrag unter die Liste zu schreiben:

Sub NeuenEintragAnhaengen()
    Dim ws As Worksheet
    Dim neueZeile As Long

    Set ws = ThisWorkbook.Worksheets("Daten")
    neueZeile = LetzteZeile(ws, "A") + 1

    ws.Cells(neueZeile, "A").Value = Date
    ws.Cells(neueZeile, "B").Value = "Neuer Eintrag"
End Sub

Sprechen Sie das Blatt immer ausdrücklich an (hier über ws). Mit ActiveSheet arbeitet das Makro auf dem Blatt, das gerade zufällig ausgewählt ist.

Vier Methoden im Vergleich

MethodeErgebnisGeeignet, wennAchtung
End(xlUp) in einer Spalteletzte belegte Zelle dieser Spalteeine Spalte immer gefüllt ist, etwa eine Nummer oder ein Datumüberspringt ausgeblendete und gefilterte Zeilen
Find("*") rückwärtsletzte Zeile mit Inhalt in irgendeiner Spaltedie Daten keine durchgehend gefüllte Spalte habenbei aktivem Autofilter unzuverlässig
UsedRange bzw. SpecialCells(xlCellTypeLastCell)Ende des „benutzten“ Bereichsnur zur groben Orientierungzählt auch Formatierungen und früher gelöschte Inhalte
Intelligente Tabelle (ListObject)Anzahl der Datensätzedie Daten als Tabelle formatiert sindnur für Tabellen

Bei einer intelligenten Tabelle (Start, Als Tabelle formatieren) brauchen Sie die letzte Zeile meist gar nicht. Excel kennt die Datensätze selbst:

Dim tbl As ListObject
Set tbl = ThisWorkbook.Worksheets("Daten").ListObjects("tblDaten")

Debug.Print tbl.ListRows.Count      'Anzahl der Datensätze
tbl.ListRows.Add                    'neue Zeile am Ende der Tabelle

Ohne VBA: letzte Zeile per Formel

Manchmal brauchen Sie die letzte Zeile gar nicht im Makro, sondern in einer Zelle, etwa für eine Überschrift wie „Daten bis Zeile 250“ oder als Grundlage für weitere Formeln. In Excel für Microsoft 365 genügt dafür:

=ZEILE(ÜBERNEHMEN(A:.A;-1))

Der Doppelpunkt mit Punkt in A:.A ist ein gekürzter Bezug. Er umfasst Spalte A nur bis zur letzten belegten Zelle, leere Zeilen darunter fallen weg. ÜBERNEHMEN mit -1 nimmt davon die letzte Zelle, ZEILE liefert ihre Nummer. Lücken in der Liste stören nicht. Für mehrere Spalten schreiben Sie A:.C, dann zählt die letzte Zeile mit Inhalt in irgendeiner der drei Spalten. In der Beispielliste unten ergibt das 15, für Spalte A allein 10.

In allen Excel-Versionen funktioniert diese Formel:

=VERWEIS(2;1/(A:A<>"");ZEILE(A:A))

Sie ist in sehr großen Dateien langsamer, weil sie die ganze Spalte durchrechnet. Wie sie arbeitet, erklärt der Beitrag Letzten Wert einer Zeile oder Spalte in Excel ermitteln. Bei Zellen, deren Formel einen leeren Text "" liefert, unterscheiden sich die beiden Formeln: Der gekürzte Bezug zählt eine solche Zelle wie End(xlUp) mit, die VERWEIS-Formel prüft den angezeigten Wert und überspringt sie.

Dynamische Arrays per VBA: Formula2 und Überlaufbereiche

Schreibt ein Makro Formeln wie SORTIEREN, EINDEUTIG oder FILTER in eine Zelle, kommt es auf die richtige Eigenschaft an. Mit .Formula behandelt Excel die Formel wie in alten Versionen und setzt den Schnittmengenoperator @ davor. Aus =SORT(UNIQUE(A2:A10)) wird dann =@SORT(UNIQUE(A2:A10)), und in der Zelle steht nur der erste Wert statt der ganzen Liste. Erst .Formula2 erzeugt einen Überlaufbereich, so wie bei der Eingabe von Hand.

Die Länge eines Überlaufbereichs ermitteln Sie über SpillingToRange, ganz ohne Suche nach der letzten Zeile:

Option Explicit

'Schreibt eine Liste ohne Doppelte nach G2 und meldet, wie weit sie reicht
Public Sub UeberlaufAuswerten()
    Dim ws As Worksheet
    Dim ergebnis As Range

    Set ws = ThisWorkbook.Worksheets("Daten")
    ws.Range("G2").Formula2 = "=SORT(UNIQUE(A2:.A1000))"   'englische Namen, Komma als Trenner

    If ws.Range("G2").HasSpill Then
        Set ergebnis = ws.Range("G2").SpillingToRange
        Debug.Print "Einträge: " & ergebnis.Rows.Count
        Debug.Print "Letzte Zeile: " & ergebnis.Row + ergebnis.Rows.Count - 1
    Else
        'z. B. #ÜBERLAUF!, weil eine Zelle im Weg ist
        Debug.Print "Kein Überlauf, die Zelle zeigt: " & ws.Range("G2").Text
    End If
End Sub

Wichtig für die Praxis:

  • Lücken in der Quelle: Der gekürzte Bezug A2:.A1000 entfernt nur leere Zellen am Ende. Eine Lücke mitten in Spalte A erscheint in der Liste als 0. Dann schreiben Sie "=SORT(UNIQUE(FILTER(A2:A1000,A2:A1000<>"""")))", die doppelten Anführungszeichen stehen in VBA für ein Anführungszeichen im Text.
  • Formel in Englisch: Formula2 erwartet wie Formula englische Funktionsnamen und Kommas. Mit deutschen Namen arbeitet Formula2Local.
  • End(xlUp) sieht den Überlauf: Die Zellen eines Überlaufbereichs haben Werte. End(xlUp) findet deshalb auch die letzte übergelaufene Zelle. Schreiben Sie aber nichts in diese Zellen, sonst zeigt die Formel #ÜBERLAUF!.
  • Ursprung finden: Für eine Zelle mitten im Überlaufbereich liefert Range("G5").SpillParent die Zelle mit der Formel, Range("G5").HasSpill ist dort ebenfalls True.

Typische Fehler

Zum Vergleich eine Beispielliste: Spalte C ist bis Zeile 15 gefüllt, Spalte A bis Zeile 10 mit einer Lücke, Zelle D20 ist nur farbig hinterlegt.

SituationEnd(xlUp)Find("*")UsedRange
Normalfall (Spalte C)151520
Lücke in Spalte A101520
Zeilen 13 bis 15 ausgeblendet121520
Autofilter blendet Zeilen 11 bis 15 aus9120
Formel mit leerem Ergebnis ("") in Zeile 18181820
Leeres Blatt1nichts gefunden1

Daraus ergeben sich vier Regeln für die Praxis:

  • Nur formatierte Zellen: UsedRange meldet Zeile 20, obwohl die Daten in Zeile 15 enden. Für Daten deshalb End(xlUp) oder Find verwenden.
  • Filter vorher aufheben: Bei aktivem Autofilter liefern sowohl End(xlUp) als auch Find falsche Werte. Heben Sie den Filter vor der Ermittlung auf, zum Beispiel mit If ws.FilterMode Then ws.ShowAllData.
  • Formeln zählen mit: Eine Formel, die "" zurückgibt, gilt als belegte Zelle. Soll sie nicht zählen, suchen Sie mit Find und LookIn:=xlValues.
  • Belegte letzte Zeile des Blatts: Ist ausgerechnet die allerletzte Zeile (1.048.576) belegt, springt End(xlUp) von dort weiter nach oben und liefert eine zu kleine Zeilennummer. Das kommt praktisch nur bei versehentlichen Einträgen vor. Wer ganz sicher gehen will, prüft die letzte Zelle vorher: If Not IsEmpty(ws.Cells(ws.Rows.Count, "A").Value) Then …. Für Spalten gilt dasselbe mit Spalte XFD.
  • Leere Spalte: End(xlUp) liefert bei einer leeren Spalte 1, auch wenn A1 leer ist. Prüfen Sie im Zweifel zusätzlich, ob die Zelle einen Inhalt hat.

Ältere Excel-Versionen

Fest eingetragene Zeilennummern wie Cells(65536, 1) oder Cells(1048576, 1) stammen aus der Zeit, als Arbeitsmappen 65.536 Zeilen hatten. Ersetzen Sie sie durch ws.Rows.Count und ws.Columns.Count, dann passt der Code zu jeder Arbeitsmappe.

Mit KI schneller zum Ziel

Die letzte Zeile gehört zu den Dingen, die in fast jedem KI-generierten Makro vorkommen. Gerade deshalb lohnt der zweite Blick. In Code von ChatGPT, Claude oder Copilot finden sich immer wieder die alten Muster aus diesem Beitrag: UsedRange.Rows.Count, das formatierte Leerzeilen mitzählt, fest eingetragene 65536, ActiveSheet statt eines ausdrücklich angesprochenen Blatts oder .Formula statt .Formula2 bei dynamischen Formeln. Der Code läuft im ersten Test, weil die Testdaten sauber sind, und versagt später bei einem aktiven Filter oder einer formatierten Zeile.

Prüfen Sie generierten Code deshalb mit den Fällen aus der Tabelle oben. Hintergründe im Beitrag VBA-Code von ChatGPT: Warum er oft nicht funktioniert, wie Entwickler mit KI-Agenten arbeiten im Beitrag KI-Coding-Agenten für VBA: Claude Code und Codex. Sind KI-erzeugte Makros bei Ihnen schon im Einsatz, prüfen wir sie im Rahmen einer Prüfung von Arbeitsmappen. Wie wir selbst KI einsetzen, um Entwicklungszeit und Kosten zu senken, steht unter KI und Automatisierung.

Aus der Praxis: Importe, die jeden Monat laufen

Die letzte Zeile ist meist ein kleiner Baustein in einem größeren Ablauf: Ein Makro holt den Export aus der Warenwirtschaft, hängt die neuen Datensätze unter die vorhandenen, prüft Dubletten und aktualisiert die Auswertung. Ob so ein Ablauf auch nach Jahren zuverlässig läuft, entscheidet sich genau an Details wie Filtern, Leerzeilen und Formaten. Solche Importe und Schnittstellen entwickeln wir so, dass sie Fehler erkennen und melden, statt stillschweigend falsche Zahlen zu liefern. Mehr dazu unter Excel-Programmierung.

Passende Beiträge

Lieber machen lassen?

Wir programmieren die Lösung für Ihre Datei.

Vom einzelnen Makro bis zur Anwendung für die ganze Abteilung. Mit KI-Unterstützung setzen wir vieles schneller und günstiger um, jede Lösung testen wir vor der Übergabe.