Ex­cel – VBA, alle Ver­sio­nen

Sehr oft ist es beim Pro­gram­mieren erforder­lich, die let­zte Zei­le oder Spal­te mit Dat­en oder ein­er enthal­te­nen Funk­tion zu find­en. Oft ist die­ser Wert wich­tig, wenn die ers­te freie Zei­le oder Spal­te be­nö­tigt wird, wo­bei in die­sem Fall die gefun­dene let­zte Zei­le/Spal­te um den Wert 1 er­höht wird.

Ne­ben der Unter­schei­dung, ob die Zei­le oder Spal­te ge­sucht wird ist noch wich­tig, ob in der ge­sam­ten Ta­bel­le oder (nur) ein­er bes­timmten Zei­le/Spal­te ge­sucht wer­den soll. Mit zwei klei­nen, selb­st de­fi­nier­ten Funk­tio­nen kön­nen Sie rasch den entsprechen­den Wert der let­zten ge­nutz­ten Zel­le fest­stellen. Und als Add-In gespe­ichert und ent­spre­chend in die Anwen­dung einge­bun­den ste­hen die bei­den UDFs (User Defi­ned Func­tions, Be­nut­zer­de­fi­nier­te Funk­tio­nen) stän­dig und auch in der Tabel­lenar­beit mit Ex­cel zur Ver­fü­gung.

▲ nach oben …

Be­stimm­te Zei­le/Spal­te

Ver­wen­den Sie den fol­gen­den Code, um die vier Funk­tio­nen im Arbeits­blatt-Mod­ul, ei­nem all­ge­meinen Mod­ul oder als Add-In zu spe­ich­ern. Durch die „spre­chen­de” Namensge­bung soll­te klar sein, wel­che Funk­tion­al­ität jew­eils dahin­ter steckt. Bei die­sen Funk­tio­nen wird da­von aus­ge­gan­gen, dass im­mer eine bes­timmte, eine de­fi­nier­te Zei­le oder Spal­te aus­gew­ertet wer­den soll. Wird der Funk­tion kein Argu­ment (in der Klam­mer) über­ge­ben, dann wird automa­tisch Zei­le 1 bzw. Spal­te 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öch­te Ih­nen 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. Ei­ni­ge Bei­spiel find­en Sie hier am Ende die­ses Bei­tra­ges.

▲ 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

Die­sen Code kön­nen Sie hier als importier­bare *.cls-Da­tei im *.zip-For­mat herun­ter­laden. Der Im­port erfol­gt im VBA-Edi­tor (StrgM) oder  Da­tei | Im­por­tie­ren…) und die Funk­tio­nen wer­den automa­tisch im Mo­dul1 einge­fügt. In der gle­ichen Da­tei find­en Sie die *.txt-Da­tei, wel­che Sie in ei­nem beliebi­gen Edi­tor (nicht Textver­ar­beitung!) öff­nen und  den In­halt an­schlie­ßend per copy and pas­te in ein Mod­ul der Map­pe einzufü­gen kön­nen.

▲ nach oben …

Ge­sam­tes Ta­bel­len­blatt

Als Er­gän­zung bzw. Alter­na­tive bie­tet sich an, dass bei fehlen­dem Funk­tions-Argu­ment nicht die ers­te Zei­le/Spal­te aus­gew­ertet wird son­dern die gan­ze Ta­bel­le als Ba­sis für die Su­che 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 …

Die­sen Code kön­nen Sie hier als importier­bare *.cls-Da­tei im *.zip-For­mat herun­ter­laden. Der Im­port erfol­gt im VBA-Edi­tor (Strg M)  oder  Da­tei | Im­por­tie­ren…) und die Funk­tio­nen wer­den automa­tisch im Mo­dul1 einge­fügt. In der gle­ichen Da­tei find­en Sie die *.txt-Da­tei, wel­che Sie in ei­nem beliebi­gen Edi­tor (nicht Textver­ar­beitung!) öff­nen und  den In­halt an­schlie­ßend per copy and pas­te in ein Mod­ul der Map­pe einzufü­gen kön­nen.

Die­se Funk­tio­nen (mit dem „All” am Ende des Funk­tion­sna­mens) sind uni­verseller ein­set­zbar als die oben ge­zeig­ten, wel­che sich ja auf eine bes­timmte Spal­te be­schrän­ken. Aus dem Grun­de stel­le ich die­se 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 pas­sen­de Ver­sion für Ex­cel 2003 bzw. ab 2007 kosten­los zuge­sandt.

Noch ein Hin­weis: Es wird in man­chen Tex­ten im Inter­net vorgeschla­gen, die Funk­tion Use­dRange zu ver­wen­den. Das ist nicht wirk­lich ziel­füh­rend und kann zu erhe­blich hö­he­ren Wer­ten und da­mit fal­schen Zah­len füh­ren.

▲ nach oben …

Di­rekt im Code

Wie bere­its oben erwäh­nt, kön­nen Sie natür­lich auch die jew­eilige Zei­le oder Spal­te di­rekt im Code ver­wen­den. Bei ein­ma­liger Fes­tle­gung des Wer­tes be­darf es dann kei­nes getren­nten Auf­rufs ein­er Funk­tion. Hier ei­ni­ge Bei­spie­le (ein­zel­ne Zei­len/An­wei­sun­gen, 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 ers­ten bei­den Zei­len wird die let­zte Zei­le der Spal­te A gefun­den, in der drit­ten Zei­le des Codes hier­über die ers­te freie Zei­le der Spal­te C. In den bei­den fol­gen­den Code-Zei­len jew­eils die let­zte bzw. ers­te freie Spal­te ein­er de­fi­nier­ten Zei­le.

Das Prin­zip des find­ens der ers­ten/letz­ten Zei­le oder Spal­te ei­nes gan­zen Arbeits­blattes ist dem weit­er oben ge­zeig­ten Code gle­ich. Hier die Bei­spie­le für die let­zte Zei­le und eine ers­te freie Spal­te:

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ög­lich­keit beste­ht, dass die let­zte Zei­le oder Spal­te des Blat­tes Dat­en oder eine Funk­tion ent­hält, dann soll­ten Sie das durch eine Prü­fung abfan­gen. Denn in eine nicht vorhan­dene Zei­le oder Spal­te kann nichts ge­schrie­ben wer­den und das führt unweiger­lich zu ei­nem Feh­ler. Hier ein Bei­spiel, wie so et­was 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 of­fen, 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.

▲ nach oben …