Sie brauchen wahrscheinlich öfter einmal die Position, den numerischen Wert der letzten beschriebenen Spalte oder Zeile einer Tabelle (nicht direkt den Inhalt). Prinzipiell ist das immer dann der Fall, wenn Sie unterhalb der letzten Zeile oder rechts der letzten Spalte neue Werte einfügen wollen. Viele Wege führen zum Ziel, gute und weniger gute …

Bitte nicht so:

Aus Unkenntnis manchen Seiteneffekts wird oft folgendes verwendet:

x = ActiveCell.SpecialCells(xlLastCell).Column
y = ActiveCell.SpecialCells(xlLastCell).Row

Diese Lösung ist aber sehr ungenau, sie liefert in vielen Fällen falsche Ergebnisse. Um das zu testen, machen Sie bitte einmal folgendes:

• Tragen Sie einige Werte in eine Tabelle ein.
• Dann geben Sie in eine Zelle, die um mindestens 1 Zeile und Spalte weiter nach rechts und und auch nach unten verschoben ist als die bisherige äußerste Position einen belie­bigen Wert ein.
• Klicken Sie nun in eine andere Zelle.
• Löschen Sie jetzt die eben eingegebenen Daten aus der „ausgegliederten“ Zelle.
• Führen Sie nun zur Kontrolle den folgenden (extrem einfach gehaltenen) Code aus:

Sub LetzteZelle()
  Dim x, y
  
  x = ActiveCell.SpecialCells(xlLastCell).Column
  y = ActiveCell.SpecialCells(xlLastCell).Row
  MsgBox "Spalte " & x & " Zeile " & y
End Sub

Hier der Code (als gepackte Textdatei) zum Kopieren und Einfügen in den VBA-Editor. Diese „Unart“ von Excel wird wohl nie beseitigt werden. Noch unangenehmer (weil schwe­­rer zu finden) ist es, wenn eine Zelle außerhalb des eigentlichen Datenbereichs eine besondere Formatierung jedoch keinen Wert hat. Diese „falsche“ Zelle wird immer „gefun­den“, auch wenn Sie die Tabelle unter einem anderen Namen speichern.

▲ nach oben …

Sondern beispielsweise so:

Ein korrektes Ergebnis erhalten Sie mit diesen beiden Code-Zeilen:

LastRow = ActiveSheet.Cells(Rows.Count, Spalte).End(xlUp).Row

und / bzw.

LastCol = ActiveSheet.Cells(Zeile, Columns.Count).End(xlToLeft).Column

wobei Sie statt Spalte bzw. Zeile den numerischen Wert der Spalte oder Zeile eingeben. Die Spalte A hat dann den numerischen Wert 1, Spalte B hat die 2, usw. Das Ergebnis bezieht sich dann stets exakt auf die Spalte/Zeile, die Sie angegeben haben. Noch einmal mit anderen Worten: Es wird nur die angegebene Zeile bzw. Spalte aus­gewertet; selbst wenn andere Zeilen bzw. Spalten weiter unten oder weiter rechts noch Eingaben haben, werden diese nicht berücksichtigt.

▲ nach oben …

Und mit „Notbremse“:

Der obige Code reicht im Normalfall vollkommen aus. Sollte aber auch nur die geringste „Gefahr“ bestehen, dass auch die letzte Zeile oder Spalte des Tabellenblatt beschrieben ist, dann sind Sie mit diesen Code-Zeilen auf der sicheren Seite (bezieht sich auf Spalte A bzw. Zeile 1):

LastRow = IIf(IsEmpty(ActiveSheet.Cells(Rows.Count, 1)), _
  ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row, _
  ActiveSheet.Rows.Count)

bzw. zur Feststellung der letzten Spalte:

LastCol = IIf(IsEmpty(ActiveSheet.Cells(1, Columns.Count)), _
  ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column, _
  ActiveSheet.Cells(1, Columns.Count).Column)

Für die letzte Zeile geht es auch so, wenn Sie die Alphanumerischen Angaben der Spalte und nicht die (rein) numerischen verwenden wollen:

LastRow = IIf(IsEmpty(ActiveSheet.Cells(Rows.Count, "A")), _
  ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row, _
  ActiveSheet.Rows.Count)

Etwas professioneller können Sie diese beiden Codeblöcke auch so schreiben:

With ActiveSheet
  Lastrow = IIf(IsEmpty(.Cells(Rows.Count, 1)), _
    .Cells(Rows.Count, 1).End(xlUp).Row, _
    .Rows.Count)
End With
With ActiveSheet
  LastCol = IIf(IsEmpty(.Cells(1, Columns.Count)), _
    .Cells(1, Columns.Count).End(xlToLeft).Column, _
    .Cells(1, Columns.Count).Column)
End With

'Bei GEMEINSAMER Variablenzuweisung etwas kürzer:
With ActiveSheet
  LastRow = IIf(IsEmpty(.Cells(Rows.Count, 1)), _
    .Cells(Rows.Count, 1).End(xlUp).Row, _
    .Rows.Count)
  LastCol = IIf(IsEmpty(.Cells(1, Columns.Count)), _
    .Cells(1, Columns.Count).End(xlToLeft).Column, _
    .Cells(1, Columns.Count).Column)
End With

Auch hier gibt es natürlich die Möglichkeit, die Spaltenbezeichnung als Zeichenkette (String) in den Code einzugeben.

Wollen Sie -ähnlich wie beim ersten Versuch- den entsprechenden Wert der kompletten Tabelle ermitteln, dann sind folgende Zeilen zielführend:

LastRowAll =Cells.Find(What:="*", _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlPrevious).Row

sowie

LastColAll =Cells.Find(What:="*", _
  SearchOrder:=xlByColumns, _
  SearchDirection:=xlPrevious).Column

▲ nach oben …

Als Funktion universell einsetzbar

Ich ziehe es vor, diese beiden Routinen in eine Funktion auszulagern, damit ich von ver­schie­denen Prozeduren oder anderen Funktionen darauf zugreifen kann. Wichtig: Was Sie hier auf dieser Seite sehen, ist mehr oder weniger nur das Prinzip. Rudimentär läuft das auch, wenn die Funktionen im gleichen Modul wie die Prozedur stehen. Professioneller und wesentlich sicherer ist der Code, wie er in der Muster-Datei bzw. den Code-Files abgelegt ist. Unsere Empfehlung: Unbedingt dort zumindest einmal reinschauen!

Public Function LastRowAll(Optional wks As Variant) As Long
  If IsMissing(wks) Then set wks = ActiveSheet
  LastRowAll = wks.Cells.Find(What:="*", _
    SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row
End Function

Public Function LastColAll(Optional wks As Variant) As Long
  If IsMissing(wks) Then set wks = ActiveSheet
  LastColAll = wks.Cells.Find(What:="*", _
  SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
End Function

Und nicht nur der Komplettheit wegen auch noch die beiden anderen Funktionen für die einzelnen Spalten/Zeilen:

Public Function LastRow(Optional Spalte, Optional Wks) As Long
   Dim AnzZe As Long
   
   If IsMissing(Spalte) Then Spalte = 1
   If Spalte = 0 Then Spalte = 1
   If IsMissing(Wks) Then Set Wks = ActiveSheet
   With Wks
      AnzZe = .Rows.Count
      LastRow = IIf(IsEmpty(.Cells(AnzZe, Spalte)), _
         .Cells(Rows.Count, Spalte).End(xlUp).Row, AnzZe)
   End With
End Function

Public Function LastCol(Optional Zeile, Optional Wks) As Long
   Dim AnzSp As Integer
   AnzSp = Columns.Count
   
   If IsMissing(Zeile) Then Zeile = 1
   If Zeile = 0 Then Zeile = 1
   If IsMissing(Wks) Then Set Wks = ActiveSheet
   With Wks
      AnzSp = .Columns.Count
      LastCol = IIf(IsEmpty(.Cells(Zeile, _
         AnzSp).End(xlToLeft).Column), _
         .Cells(Zeile, AnzSp).End(xlToLeft).Column, AnzSp)
   End With
End Function

Wichtiger Hinweis: Vielfach wird ja die letzte Zeile gesucht, um die erste freie Zeile zu bestimmen. In den allermeisten Fällen wird auch der obige Code vollkommen ausreichen, wenn Sie (beispielsweise)

FirstFreeRow = LastRow + 1

im Code verwenden. Wenn Sie aber „Auf Nummer Sicher“ gehen wollen, also alle erdenk­lichen Fehler erst gar nicht zulassen möchten, dann bietet sich die Feststellung der ersten freien Zeile so an:

If LastRow = ActiveSheet.Rows.Count Then
  MsgBox "Es ist keine Zeile mehr frei"
  Exit Sub
End If

Dann wird der Ablauf des Programms „sauber“ mit einer Fehlermeldung beendet und eigentlich weiß dann jeder User, woran es wirklich liegt. – Diesen Code biete ich nicht zum Download an, die vier kurzen Zeilen lassen sich ganz gut per c:p einfügen oder schnell mal abtippen.

▲ nach oben …

Download-Übersicht

In diesem Beitrag werden Ihnen eine oder mehrere Files zum Download angeboten. In der folgenden Tabelle ist jede Datei mit verschiedenen Informationen aufgeführt. Ein Klick auf den Link in der ersten (linken) Spalte startet den Download von unserem Server.

Hinweise:

Selbstredend können Sie dann daraus auch ein Add-In erstellen, damit diese Funk­tio­na­li­tät in allen Workbooks auf Ihrem Rechner zur Verfügung steht. Auf Wunsch senden wir Ihnen gerne das fertige Add-In gegen eine Aufwandsentschädigung zu, einfach eine Mail senden …

Hinweis: Diese Thematik wird in diesem Blog noch einmal hier mit ähnlicher Problemstellung diskutiert.