Sie brau­chen wahrschein­lich öf­ter ein­mal die Posi­tion, den nu­me­ri­schen Wert der let­zten beschriebe­nen Spal­te oder Zei­le ein­er Ta­bel­le (nicht di­rekt den In­halt). Prinzip­iell ist das im­mer dann der Fall, wenn Sie unter­halb der let­zten Zei­le oder rechts der let­zten Spal­te neue Wer­te ein­fü­gen wol­len. Vie­le Wege füh­ren zum Ziel, gute und we­ni­ger gute …

Bit­te nicht so:

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

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

Die­se Lö­sung ist aber sehr unge­nau, sie lie­fert in vie­len Fäl­len fal­sche Ergeb­nisse. Um das zu tes­ten, ma­chen Sie bit­te ein­mal fol­gen­des:

• Tra­gen Sie ei­ni­ge Wer­te in eine Ta­bel­le ein.
• Dann ge­ben Sie in eine Zel­le, die um min­destens 1 Zei­le und Spal­te weit­er nach rechts und und auch nach un­ten ver­schoben ist als die bish­erige äußer­ste Posi­tion ei­nen belie­bigen Wert ein.
• Klick­en Sie nun in eine an­de­re Zel­le.
• Lö­schen Sie jet­zt die eben eingegebe­nen Dat­en aus der „aus­gegliederten” Zel­le.
• Füh­ren Sie nun zur Kon­trolle den fol­gen­den (ex­trem 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 Ko­pie­ren und Ein­fü­gen in den VBA-Edi­tor. Die­se „Un­art” von Ex­cel wird wohl nie beseit­igt wer­den. Noch unan­genehmer (weil schwe­­rer zu find­en) ist es, wenn eine Zel­le außer­halb des ei­gent­li­chen Daten­bere­ichs eine beson­dere For­matierung je­doch kei­nen Wert hat. Die­se „fal­sche” Zel­le wird im­mer „gefun­den”, auch wenn Sie die Ta­bel­le un­ter ei­nem an­de­ren Na­men spe­ich­ern.

▲ nach oben …

Son­dern bei­spiels­wei­se so:

Ein kor­rek­tes Ergeb­nis erhal­ten Sie mit die­sen bei­den Code-Zei­len:

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

und / bzw.

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

wo­bei Sie statt Spal­te bzw. Zei­le den nu­me­ri­schen Wert der Spal­te oder Zei­le ein­ge­ben. Die Spal­te A hat dann den nu­me­ri­schen Wert 1, Spal­te B hat die 2, usw. Das Ergeb­nis be­zieht sich dann stets ex­akt auf die Spal­te/Zei­le, die Sie an­ge­ge­ben ha­ben. Noch ein­mal mit an­de­ren Wor­ten: Es wird nur die an­ge­ge­be­ne Zei­le bzw. Spal­te aus­gewertet; selb­st wenn an­de­re Zei­len bzw. Spal­ten weit­er un­ten oder weit­er rechts noch Ein­ga­ben ha­ben, wer­den die­se nicht berück­sichtigt.

▲ nach oben …

Und mit „Not­brem­se”:

Der obi­ge Code reicht im Nor­mal­fall vol­lkom­men aus. Soll­te aber auch nur die ger­ing­ste „Ge­fahr” beste­hen, dass auch die let­zte Zei­le oder Spal­te des Tabel­len­blatt be­schrie­ben ist, dann sind Sie mit die­sen Code-Zei­len auf der si­che­ren Sei­te (be­zieht sich auf Spal­te A bzw. Zei­le 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 Spal­te:

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 Zei­le geht es auch so, wenn Sie die Alphanu­merischen An­ga­ben der Spal­te und nicht die (rein) nu­me­ri­schen ver­wen­den wol­len:

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

Et­was pro­fes­sioneller kön­nen Sie die­se bei­den Code­blöcke auch so schrei­ben:

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ög­lich­keit, die Spal­tenbeze­ich­nung als Zeichen­kette (String) in den Code ein­zu­ge­ben.

Wol­len Sie ‑ähn­lich wie beim ers­ten Ver­such- den entsprechen­den Wert der kom­plet­ten Ta­bel­le ermit­teln, dann sind fol­gende Zei­len ziel­füh­rend:

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

so­wie

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

▲ nach oben …

Als Funk­ti­on uni­ver­sell ein­setz­bar

Ich zie­he es vor, die­se bei­den Rou­ti­nen in eine Funk­tion auszu­lagern, da­mit ich von ver­schie­denen Proze­duren oder an­de­ren Funk­tio­nen dar­auf zu­grei­fen kann. Wich­tig: Was Sie hier auf die­ser Sei­te se­hen, ist mehr oder we­ni­ger nur das Prin­zip. 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 we­sent­lich sicher­er ist der Code, wie er in der Mus­ter-Da­tei bzw. den Code-Files ab­ge­legt ist. Un­se­re Emp­feh­lung: 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 we­gen auch noch die bei­den an­de­ren Funk­tio­nen für die einzel­nen Spal­ten/Zei­len:

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

Wich­ti­ger Hin­weis: Viel­fach wird ja die let­zte Zei­le ge­sucht, um die ers­te freie Zei­le zu bes­tim­men. In den aller­meis­ten Fäl­len wird auch der obi­ge 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” ge­hen wol­len, also alle erdenk­lichen Feh­ler erst gar nicht zu­las­sen möcht­en, dann bie­tet sich die Fest­stel­lung der ers­ten frei­en Zei­le so an:

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

Dann wird der Ab­lauf des Pro­gramms „sau­ber” mit ein­er Fehler­mel­dung been­det und ei­gent­lich weiß dann jed­er User, wor­an es wirk­lich liegt. – Die­sen Code bie­te ich nicht zum Down­load an, die vier kur­zen Zei­len las­sen sich ganz gut per C:P ein­fü­gen oder schnell mal abtip­pen.

▲ nach oben …

Down­load-Über­sicht

In die­sem Bei­trag wer­den Ih­nen eine oder meh­re­re Files zum Down­load ange­boten. In der fol­gen­den Ta­bel­le ist jede Da­tei mit ver­schiede­nen Infor­ma­tio­nen aufge­führt. Ein Klick auf den Link in der ers­ten (lin­ken) Spal­te star­tet den Down­load von un­se­rem Serv­er.

Hin­weise:

Selb­stre­dend kön­nen Sie dann dar­aus auch ein Add-In er­stel­len, da­mit die­se Funk­tio­na­li­tät in al­len Work­books auf Ih­rem Rech­n­er zur Ver­fü­gung ste­ht. Auf Wun­sch sen­den wir Ih­nen ger­ne das fer­tige Add-In ge­gen eine Aufwand­sentschädi­gung zu, ein­fach eine Mail sen­den …

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