Sie brauchen wahrschein­lich öfter ein­mal die Posi­tion, den numerischen Wert der let­zten beschriebe­nen Spalte oder Zeile ein­er Tabelle (nicht direkt den Inhalt). Prinzip­iell ist das immer dann der Fall, wenn Sie unter­halb der let­zten Zeile oder rechts der let­zten Spalte neue Werte ein­fü­gen wollen. Viele Wege führen zum Ziel, gute und weniger gute …

Bitte nicht so:

Aus Unken­nt­nis manchen Seit­en­ef­fek­ts wird oft fol­gen­des ver­wen­det:

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

Diese Lösung ist aber sehr unge­nau, sie liefert in vie­len Fällen falsche Ergeb­nisse. Um das zu testen, machen Sie bitte ein­mal fol­gen­des:

• Tra­gen Sie einige Werte in eine Tabelle ein.
• Dann geben Sie in eine Zelle, die um min­destens 1 Zeile und Spalte weit­er nach rechts und und auch nach unten ver­schoben ist als die bish­erige äußer­ste Posi­tion einen belie­bigen Wert ein.
• Klick­en Sie nun in eine andere Zelle.
• Löschen Sie jet­zt die eben eingegebe­nen Dat­en aus der „aus­gegliederten” Zelle.
• Führen Sie nun zur Kon­trolle den fol­gen­den (extrem ein­fach gehal­te­nen) 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 gepack­te Text­datei) zum Kopieren und Ein­fü­gen in den VBA-Edi­tor. Diese „Unart” von Excel wird wohl nie beseit­igt wer­den. Noch unan­genehmer (weil schwe­­rer zu find­en) ist es, wenn eine Zelle außer­halb des eigentlichen Daten­bere­ichs eine beson­dere For­matierung jedoch keinen Wert hat. Diese „falsche” Zelle wird immer „gefun­den”, auch wenn Sie die Tabelle unter einem anderen Namen spe­ich­ern.

▲ nach oben …

Sondern beispielsweise so:

Ein kor­rek­tes Ergeb­nis erhal­ten Sie mit diesen bei­den 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 Ergeb­nis bezieht sich dann stets exakt auf die Spalte/Zeile, die Sie angegeben haben. Noch ein­mal mit anderen Worten: Es wird nur die angegebene Zeile bzw. Spalte aus­gewertet; selb­st wenn andere Zeilen bzw. Spal­ten weit­er unten oder weit­er rechts noch Eingaben haben, wer­den diese nicht berück­sichtigt.

▲ nach oben …

Und mit „Notbremse”:

Der obige Code reicht im Nor­mal­fall vol­lkom­men aus. Sollte aber auch nur die ger­ing­ste „Gefahr” beste­hen, dass auch die let­zte Zeile oder Spalte des Tabel­len­blatt 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 Fest­stel­lung der let­zten 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 let­zte Zeile geht es auch so, wenn Sie die Alphanu­merischen Angaben der Spalte und nicht die (rein) numerischen ver­wen­den wollen:

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

Etwas pro­fes­sioneller kön­nen Sie diese bei­den Code­blö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ür­lich die Möglichkeit, die Spal­tenbeze­ich­nung als Zeichen­kette (String) in den Code einzugeben.

Wollen Sie ‑ähn­lich wie beim ersten Ver­such- den entsprechen­den Wert der kom­plet­ten Tabelle ermit­teln, dann sind fol­gende 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 bei­den Rou­ti­nen in eine Funk­tion auszu­lagern, damit ich von ver­schie­denen Proze­duren oder anderen Funk­tio­nen darauf zugreifen kann. Wichtig: Was Sie hier auf dieser Seite sehen, ist mehr oder weniger nur das Prinzip. Rudi­men­tär läuft das auch, wenn die Funk­tio­nen im gle­ichen Mod­ul wie die Proze­dur ste­hen. Pro­fes­sioneller und wesentlich sicher­er ist der Code, wie er in der Muster-Datei bzw. den Code-Files abgelegt ist. Unsere Empfehlung: Unbe­d­ingt dort zumin­d­est ein­mal rein­schauen!

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 Kom­plet­theit wegen auch noch die bei­den anderen Funk­tio­nen für die einzel­nen 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 Hin­weis: Vielfach wird ja die let­zte Zeile gesucht, um die erste freie Zeile zu bes­tim­men. In den aller­meis­ten Fällen wird auch der obige Code vol­lkom­men aus­re­ichen, wenn Sie (beispiel­sweise)

FirstFreeRow = LastRow + 1

im Code ver­wen­den. Wenn Sie aber „Auf Num­mer Sich­er” gehen wollen, also alle erdenk­lichen Fehler erst gar nicht zulassen möcht­en, dann bietet sich die Fest­stel­lung 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 Pro­gramms „sauber” mit ein­er Fehler­mel­dung been­det und eigentlich weiß dann jed­er User, woran es wirk­lich liegt. – Diesen Code biete ich nicht zum Down­load an, die vier kurzen Zeilen lassen sich ganz gut per C:P ein­fü­gen oder schnell mal abtip­pen.

▲ nach oben …

Download-Übersicht

In diesem Beitrag wer­den Ihnen eine oder mehrere Files zum Down­load ange­boten. In der fol­gen­den Tabelle ist jede Datei mit ver­schiede­nen Infor­ma­tio­nen aufge­führt. Ein Klick auf den Link in der ersten (linken) Spalte startet den Down­load von unserem Serv­er.

Hin­weise:

Selb­stre­dend kön­nen Sie dann daraus auch ein Add-In erstellen, damit diese Funk­tio­na­li­tät in allen Work­books auf Ihrem Rech­n­er zur Ver­fü­gung ste­ht. Auf Wun­sch senden wir Ihnen gerne das fer­tige Add-In gegen eine Aufwand­sentschädi­gung zu, ein­fach eine Mail senden …

Hin­weis: Diese The­matik wird in diesem Blog noch ein­mal hier mit ähn­lich­er Prob­lem­stel­lung disku­tiert.