Excel – VBA, alle Versionen

Sehr oft ist es beim Programmieren erforderlich, die letzte Zeile oder Spalte mit Daten oder einer enthaltenen Funktion zu finden. Oft ist dieser Wert wichtig, wenn die erste freie Zeile oder Spalte benötigt wird, wobei in diesem Fall die gefundene letzte Zeile/Spalte um den Wert 1 erhöht wird.

Neben der Unterscheidung, ob die Zeile oder Spalte gesucht wird ist noch wichtig, ob in der gesamten Tabelle oder (nur) einer bestimmten Zeile/Spalte gesucht werden soll. Mit zwei kleinen, selbst definierten Funktionen können Sie rasch den entsprechenden Wert der letzten genutzten Zelle feststellen. Und als Add-In gespeichert und entsprechend in die Anwendung eingebunden stehen die beiden UDFs (User Defined Functions, Benutzerdefinierte Funktionen) ständig und auch in der Tabellenarbeit mit Excel zur Verfügung.

▲ nach oben …

Bestimmte Zeile/Spalte

Verwenden Sie den folgenden Code, um die vier Funktionen im Arbeitsblatt-Modul, einem allgemeinen Modul oder als Add-In zu speichern. Durch die “sprechende” Namensgebung sollte klar sein, welche Funktionalität jeweils dahinter steckt. Bei diesen Funktionen wird davon ausgegangen, dass immer eine bestimmte, eine definierte Zeile oder Spalte ausgewertet werden soll. Wird der Funktion kein Argument (in der Klammer) übergeben, dann wird automatisch Zeile 1 bzw. Spalte A als Wert eingesetzt bzw. verwendet. Eine Alternative dazu finden Sie nach dem folgenden Code-Block.

Ich möchte Ihnen nicht verschweigen, dass die den Kern jeder der Funktionen natürlich auch im ganz normalen Code verwenden können. Einige Beispiel finden Sie hier am Ende dieses Beitrages.

▲ nach oben …

Option Explicit

Public Function LastRow(Optional Spalte) As Long
  If IsMissing(Spalte) Then Spalte = 1
  LastRow = ActiveSheet.Cells(Rows.Count, Spalte).End(xlUp).Row
End Function

Public Function LastCol(Optional Zeile) As Long
  If IsMissing(Zeile) Then Zeile = 1
  LastCol = ActiveSheet.Cells(Zeile, _
   Columns.Count).End(xlToLeft).Column
End Function

Public Function FirstNewRow(Optional Spalte) As Long
  Dim Rc As Long
  If IsMissing(Spalte) Then Spalte = 1
  Rc = ActiveSheet.Cells(Rows.Count, Spalte).End(xlUp).Row
  If Rc >= Rows.Count Then
    Rc = 1
    MsgBox "Es ist keine Spalte mehr frei!" & vbCrLf _
     & "Es wird der Wert 1 zurückgegeben."
  End If
  LastRow = Rc
End Function

Public Function FirstNewCol(Optional Zeile) As Long
  Dim Rc As Long
  If IsMissing(Zeile) Then Zeile = 1
  Rc = ActiveSheet.Cells(Zeile, Columns.Count).End(xlToLeft).Column
  If Rc >= Columns.Count Then
    Rc = 1
    'MsgBox "Es ist keine Spalte mehr frei!" & vbCrLf _
     & "Es wird der Wert 1 zurückgegeben."
  End If
  FirstNewCol = Rc
End Function

Diesen Code können Sie hier als importierbare *.cls-Datei im *.zip-Format herunterladen. Der Import erfolgt im VBA-Editor (StrgM) oder  Datei | Importieren…) und die Funktionen werden automatisch im Modul1 eingefügt. In der gleichen Datei finden Sie die *.txt-Datei, welche Sie in einem beliebigen Editor (nicht Textverarbeitung!) öffnen und  den Inhalt anschließend per copy and paste in ein Modul der Mappe einzufügen können.

▲ nach oben …

Gesamtes Tabellenblatt

Als Ergänzung bzw. Alternative bietet sich an, dass bei fehlendem Funktions-Argument nicht die erste Zeile/Spalte ausgewertet wird sondern die ganze Tabelle als Basis für die Suche verwendet wird. Der Code könnte dann so aussehen:

Option Explicit

Function LastRowAll() As Long
  LastRowAll = ActiveSheet.Cells.Find(What:="*", _
   SearchOrder:=xlByRows, _
   SearchDirection:=xlPrevious).Row
End Function

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

Function FirstNewRowAll() As Long
  Dim Rc As Long
  With ActiveSheet
    Rc = .Cells.Find(What:="*", _
     SearchOrder:=xlByRows, _
     SearchDirection:=xlPrevious).Row + 1
    If Rc > .Rows.Count Then
      Rc = 1
      MsgBox "Es ist keine Zeile mehr frei!" & vbCrLf _
       & "Es wird der Wert 1 zurückgegeben.", _
       vbCritical + vbOKOnly
    End If
  End With
  FirstNewRowAll = Rc
End Function

Function FirstNewColAll()
  Dim Rc As Long
  With ActiveSheet
    Rc = .Cells.Find(What:="*", _
     SearchOrder:=xlByColumns, _
     SearchDirection:=xlPrevious).Column + 1
    If Rc > .Columns.Count Then
      Rc = 1
      MsgBox "Es ist keine Spalte mehr frei!" & vbCrLf _
       & "Es wird der Wert 1 zurückgegeben.", _
       vbCritical + vbOKOnly
    End If
  End With
  FirstNewColAll = Rc
End Function

▲ nach oben …

Diesen Code können Sie hier als importierbare *.cls-Datei im *.zip-Format herunterladen. Der Import erfolgt im VBA-Editor (Strg M)  oder  Datei | Importieren…) und die Funktionen werden automatisch im Modul1 eingefügt. In der gleichen Datei finden Sie die *.txt-Datei, welche Sie in einem beliebigen Editor (nicht Textverarbeitung!) öffnen und  den Inhalt anschließend per copy and paste in ein Modul der Mappe einzufügen können.

Diese Funktionen (mit dem “All” am Ende des Funktionsnamens) sind universeller einsetzbar als die oben gezeigten, welche sich ja auf eine bestimmte Spalte beschränken. Aus dem Grunde stelle ich diese vier Funktionen auch als Add-In zur Verfügung. Fragen Sie einfach per e-Mail an, Sie bekommen dann die für Sie passende Version für Excel 2003 bzw. ab 2007 kostenlos zugesandt.

Noch ein Hinweis: Es wird in manchen Texten im Internet vorgeschlagen, die Funktion UsedRange zu verwenden. Das ist nicht wirklich zielführend und kann zu erheblich höheren Werten und damit falschen Zahlen führen.

▲ nach oben …

Direkt im Code

Wie bereits oben erwähnt, können Sie natürlich auch die jeweilige Zeile oder Spalte direkt im Code verwenden. Bei einmaliger Festlegung des Wertes bedarf es dann keines getrennten Aufrufs einer Funktion. Hier einige Beispiele (einzelne Zeilen/Anweisungen, nicht als fortlaufender Code!):

LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
FirstFreeRow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row +1
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
FirstFreeCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column +1

In den ersten beiden Zeilen wird die letzte Zeile der Spalte A gefunden, in der dritten Zeile des Codes hierüber die erste freie Zeile der Spalte C. In den beiden folgenden Code-Zeilen jeweils die letzte bzw. erste freie Spalte einer definierten Zeile.

Das Prinzip des findens der ersten/letzten Zeile oder Spalte eines ganzen Arbeitsblattes ist dem weiter oben gezeigten Code gleich. Hier die Beispiele für die letzte Zeile und eine erste freie Spalte:

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

FirstFreeCol = ActiveSheet.Cells.Find(What:="*", _
 SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious).Column + 1

Wenn auch nur im geringsten die Möglichkeit besteht, dass die letzte Zeile oder Spalte des Blattes Daten oder eine Funktion enthält, dann sollten Sie das durch eine Prüfung abfangen. Denn in eine nicht vorhandene Zeile oder Spalte kann nichts geschrieben werden und das führt unweigerlich zu einem Fehler. Hier ein Beispiel, wie so etwas realisiert werden kann:

With ActiveSheet
  FirstFreeRow = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
   .Cells(.Rows.Count, 1).End(xlUp).Row+1, .Rows.Count)
   'Es wird u.U. allerdings die letzte Zeile überschrieben
End With

Für Fragen oder Wünsche sind wir stets offen, einfach eine Mail senden.

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

▲ nach oben …