Letzte Zeile mit sichtbarem Inhalt

Ei­gent­lich ist es ganz ein­fach, per VBA die let­zte Zei­le ein­er bes­timmten Spal­te fest­zu­stel­len. Den Stan­dard da­für kön­nen Sie hier im Blog nach­le­sen. Ei­gent­lich, wie ge­sagt. Denn es gibt dur­chaus Fäl­le, wo die­se Meth­o­d­en ver­sagen müs­sen. Näm­lich im­mer dann, wenn in min­destens ein­er Zel­le unter­halb der let­zten sicht­baren Zel­le eine For­mel ste­ht, die als Ergeb­nis ei­nen Leer­String ("") er­zeugt. 

Um kei­ne Unklarheit­en aufkom­men zu las­sen: Es sind kei­ne Zel­len aus­ge­blendet oder der In­halt durch For­matierung unsicht­bar ge­macht wor­den. Ma­chen Sie ger­ne fol­gen­den Ver­such: Fül­len Sie A1:A30 mit (beispiel­sweise) die­ser For­mel, wel­che in je­dem Fall ei­nen Leer­String als Ergeb­nis zu­rück gibt: =WENN(ZEILE()>0; ""; "x"). Es ist lo­gisch, dass dort kein x er­schei­nen wird, denn eine Zeilen­num­mer ist im­mer grö­ßer als 0. Und da­mit es ei­nen Sinn er­gibt, über­schreiben Sie eine oder meh­re­re Zel­len in A1:A25 mit ei­nem Text oder ein­er Zahl. Es blei­ben also min­destens die unter­sten 5 Zel­len mit der For­mel.

Ver­suchen Sie jet­zt ein­mal mit Stan­dard-Mit­teln wie beispiel­sweise Strg↓ oder per VBA die let­zte Zei­le mit sicht­barem In­halt her­auszufind­en. Es wir im­mer 30 her­aus kom­men. Bei Ex­cel­for­meln.de gibt es eine For­mel-Lö­sung, die auch funk­tion­iert. 

Es gibt von uns auch eine Lö­sung für VBA. Ei­gent­lich sog­ar zwei, denn die eine ist „nur” eine Proze­dur, die zwei­te eine Funk­tion. Bei­de lie­fern das gewün­schte Ergeb­nis. Die Proze­dur soll­ten Sie in VBA-Edi­tor  in dem Blatt ein­fü­gen, wo die Berech­nung stat­tfind­en soll. Die Funk­tion ge­hört sin­nvoller­weise in ein all­ge­meines Mod­ul, da­mit sie von Proze­duren oder auch di­rekt inner­halb ein­er beliebi­gen Zel­le der Map­pe auf­ge­ru­fen wer­den kann. Inner­halb ein­er Proze­dur kön­nte der Auf­ruf dann so ausse­hen:

LetzteSichtbare = Modul1.LastFilledCell("C")

oder falls Sie den nu­me­ri­schen Wert der Spal­te ver­wen­den:

LetzteSichtbare = Modul1.LastFilledCell(3)

Ein Auf­ruf inner­halb der Ar­beits­map­pe er­war­tet nur den Funk­tion­sna­men und als Argu­ment eine gül­ti­ge Spal­tenbeze­ich­nung. Hier nun (auch zum ko­pie­ren) den Code für die Proze­dur und die Funk­tion:

Sie kön­nen die­sen Code in die­ser *.zip-Da­tei her­un­ter la­den und in Ihr VBA-Pro­jekt ein­binden, Änderun­gen sind selb­stre­dend er­laubt.

Er­gän­zung: Falls Sie meh­re­re Spal­ten aus­wer­ten wol­len, dann bie­tet sich die­ser Code an; allerd­ings ohne Fehler­prü­fung (kann natür­lich „nach­ge­rüs­tet” wer­den):

Sub LetzteBeschreibeneZeile2()
   Dim LetzteInhaltZeile As Long
   With ActiveSheet.Range("C:AB") 'Anpassen
      LetzteInhaltZeile = .Find(What:="*", _
         After:=.Cells(1), _
         LookIn:=xlValues, _
         LookAt:=xlWhole, _
         SearchOrder:=xlByRows, _
         SearchDirection:=xlPrevious, _
         MatchCase:=False).Row
   End With
   MsgBox LetzteInhaltZeile
End Sub

Die­se Zei­len ste­hen Ih­nen hier nur per copy/pas­te zur Ver­fü­gung, sie sind nicht in der zip-Da­tei. Aber das soll­te mach­bar sein …  😉