Excel – VBA, alle Ver­sio­nen

Sehr oft ist es beim Pro­gram­mieren erforder­lich, die let­zte Zeile oder Spalte mit Dat­en oder ein­er enthal­te­nen Funk­tion zu find­en. Oft ist dieser Wert wichtig, wenn die erste freie Zeile oder Spalte benötigt wird, wobei in diesem Fall die gefun­dene let­zte Zeile/Spalte um den Wert 1 erhöht wird.

Neben der Unter­schei­dung, ob die Zeile oder Spalte gesucht wird ist noch wichtig, ob in der gesamten Tabelle oder (nur) ein­er bes­timmten Zeile/Spalte gesucht wer­den soll. Mit zwei kleinen, selb­st definierten Funk­tio­nen kön­nen Sie rasch den entsprechen­den Wert der let­zten genutzten Zelle fest­stellen. Und als Add-In gespe­ichert und entsprechend in die Anwen­dung einge­bun­den ste­hen die bei­den UDFs (User Defined Func­tions, Benutzerdefinierte Funk­tio­nen) ständig und auch in der Tabel­lenar­beit mit Excel zur Ver­fü­gung.

▲ nach oben …

Bestimmte Zeile/Spalte

Ver­wen­den Sie den fol­gen­den Code, um die vier Funk­tio­nen im Arbeits­blatt-Mod­ul, einem all­ge­meinen Mod­ul oder als Add-In zu spe­ich­ern. Durch die „sprechende” Namensge­bung sollte klar sein, welche Funk­tion­al­ität jew­eils dahin­ter steckt. Bei diesen Funk­tio­nen wird davon aus­ge­gan­gen, dass immer eine bes­timmte, eine definierte Zeile oder Spalte aus­gew­ertet wer­den soll. Wird der Funk­tion kein Argu­ment (in der Klam­mer) übergeben, dann wird automa­tisch Zeile 1 bzw. Spalte A als Wert einge­set­zt bzw. ver­wen­det. Eine Alter­na­tive dazu find­en Sie nach dem fol­gen­den Code-Block.

Ich möchte Ihnen nicht ver­schweigen, dass die den Kern jed­er der Funk­tio­nen natür­lich auch im ganz nor­malen Code ver­wen­den kön­nen. Einige Beispiel find­en 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ön­nen Sie hier als importier­bare *.cls-Datei im *.zip-For­mat herun­ter­laden. Der Import erfol­gt im VBA-Edi­tor (StrgM) oder  Datei | Importieren…) und die Funk­tio­nen wer­den automa­tisch im Modul1 einge­fügt. In der gle­ichen Datei find­en Sie die *.txt-Datei, welche Sie in einem beliebi­gen Edi­tor (nicht Textver­ar­beitung!) öff­nen und  den Inhalt anschließend per copy and paste in ein Mod­ul der Mappe einzufü­gen kön­nen.

▲ nach oben …

Gesamtes Tabellenblatt

Als Ergänzung bzw. Alter­na­tive bietet sich an, dass bei fehlen­dem Funk­tions-Argu­ment nicht die erste Zeile/Spalte aus­gew­ertet wird son­dern die ganze Tabelle als Basis für die Suche ver­wen­det wird. Der Code kön­nte dann so ausse­hen:

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ön­nen Sie hier als importier­bare *.cls-Datei im *.zip-For­mat herun­ter­laden. Der Import erfol­gt im VBA-Edi­tor (Strg M)  oder  Datei | Importieren…) und die Funk­tio­nen wer­den automa­tisch im Modul1 einge­fügt. In der gle­ichen Datei find­en Sie die *.txt-Datei, welche Sie in einem beliebi­gen Edi­tor (nicht Textver­ar­beitung!) öff­nen und  den Inhalt anschließend per copy and paste in ein Mod­ul der Mappe einzufü­gen kön­nen.

Diese Funk­tio­nen (mit dem „All” am Ende des Funk­tion­sna­mens) sind uni­verseller ein­set­zbar als die oben gezeigten, welche sich ja auf eine bes­timmte Spalte beschränken. Aus dem Grunde stelle ich diese vier Funk­tio­nen auch als Add-In zur Ver­fü­gung. Fra­gen Sie ein­fach per e‑Mail an, Sie bekom­men dann die für Sie passende Ver­sion für Excel 2003 bzw. ab 2007 kosten­los zuge­sandt.

Noch ein Hin­weis: Es wird in manchen Tex­ten im Inter­net vorgeschla­gen, die Funk­tion Use­dRange zu ver­wen­den. Das ist nicht wirk­lich zielführend und kann zu erhe­blich höheren Werten und damit falschen Zahlen führen.

▲ nach oben …

Direkt im Code

Wie bere­its oben erwäh­nt, kön­nen Sie natür­lich auch die jew­eilige Zeile oder Spalte direkt im Code ver­wen­den. Bei ein­ma­liger Fes­tle­gung des Wertes bedarf es dann keines getren­nten Aufrufs ein­er Funk­tion. Hier einige Beispiele (einzelne Zeilen/Anweisungen, nicht als fort­laufend­er 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 bei­den Zeilen wird die let­zte Zeile der Spalte A gefun­den, in der drit­ten Zeile des Codes hierüber die erste freie Zeile der Spalte C. In den bei­den fol­gen­den Code-Zeilen jew­eils die let­zte bzw. erste freie Spalte ein­er definierten Zeile.

Das Prinzip des find­ens der ersten/letzten Zeile oder Spalte eines ganzen Arbeits­blattes ist dem weit­er oben gezeigten Code gle­ich. Hier die Beispiele für die let­zte 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 ger­ing­sten die Möglichkeit beste­ht, dass die let­zte Zeile oder Spalte des Blattes Dat­en oder eine Funk­tion enthält, dann soll­ten Sie das durch eine Prü­fung abfan­gen. Denn in eine nicht vorhan­dene Zeile oder Spalte kann nichts geschrieben wer­den und das führt unweiger­lich zu einem Fehler. Hier ein Beispiel, wie so etwas real­isiert wer­den 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 Fra­gen oder Wün­sche sind wir stets offen, 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.

▲ nach oben …