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
| Methode | Ergebnis | Geeignet, wenn | Achtung |
|---|---|---|---|
End(xlUp) in einer Spalte | letzte belegte Zelle dieser Spalte | eine Spalte immer gefüllt ist, etwa eine Nummer oder ein Datum | überspringt ausgeblendete und gefilterte Zeilen |
Find("*") rückwärts | letzte Zeile mit Inhalt in irgendeiner Spalte | die Daten keine durchgehend gefüllte Spalte haben | bei aktivem Autofilter unzuverlässig |
UsedRange bzw. SpecialCells(xlCellTypeLastCell) | Ende des „benutzten“ Bereichs | nur zur groben Orientierung | zählt auch Formatierungen und früher gelöschte Inhalte |
Intelligente Tabelle (ListObject) | Anzahl der Datensätze | die Daten als Tabelle formatiert sind | nur 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:.A1000entfernt 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:
Formula2erwartet wieFormulaenglische Funktionsnamen und Kommas. Mit deutschen Namen arbeitetFormula2Local. - 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").SpillParentdie Zelle mit der Formel,Range("G5").HasSpillist dort ebenfallsTrue.
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.
| Situation | End(xlUp) | Find("*") | UsedRange |
|---|---|---|---|
| Normalfall (Spalte C) | 15 | 15 | 20 |
| Lücke in Spalte A | 10 | 15 | 20 |
| Zeilen 13 bis 15 ausgeblendet | 12 | 15 | 20 |
| Autofilter blendet Zeilen 11 bis 15 aus | 9 | 1 | 20 |
Formel mit leerem Ergebnis ("") in Zeile 18 | 18 | 18 | 20 |
| Leeres Blatt | 1 | nichts gefunden | 1 |
Daraus ergeben sich vier Regeln für die Praxis:
- Nur formatierte Zellen:
UsedRangemeldet Zeile 20, obwohl die Daten in Zeile 15 enden. Für Daten deshalbEnd(xlUp)oderFindverwenden. - Filter vorher aufheben: Bei aktivem Autofilter liefern sowohl
End(xlUp)als auchFindfalsche Werte. Heben Sie den Filter vor der Ermittlung auf, zum Beispiel mitIf 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 mitFindundLookIn:=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.
