Letzte Zeile mit sichtbarem Inhalt

Eigentlich ist es ganz einfach, per VBA die letzte Zeile einer bestimmten Spalte festzustellen. Den Standard dafür können Sie hier im Blog nachlesen. Eigentlich, wie gesagt. Denn es gibt durchaus Fälle, wo diese Methoden versagen müssen. Nämlich immer dann, wenn in mindestens einer Zelle unterhalb der letzten sichtbaren Zelle eine Formel steht, die als Ergebnis einen Leerstring ("") erzeugt. 

Um keine Unklarheiten aufkommen zu lassen: Es sind keine Zellen ausgeblendet oder der Inhalt durch Formatierung unsichtbar gemacht worden. Machen Sie gerne folgenden Versuch: Füllen Sie A1:A30 mit (beispielsweise) dieser Formel, welche in jedem Fall einen Leerstring als Ergebnis zurück gibt: =WENN(ZEILE()>0; ""; "x"). Es ist logisch, dass dort kein x erscheinen wird, denn eine Zeilennummer ist immer größer als 0. Und damit es einen Sinn ergibt, überschreiben Sie eine oder mehrere Zellen in A1:A25 mit einem Text oder einer Zahl. Es bleiben also mindestens die untersten 5 Zellen mit der Formel.

Versuchen Sie jetzt einmal mit Standard-Mitteln wie beispielsweise Strg oder per VBA die letzte Zeile mit sichtbarem Inhalt herauszufinden. Es wir immer 30 heraus kommen. Bei Excelformeln.de gibt es eine Formel-Lösung, die auch funktioniert. 

Es gibt von uns auch eine Lösung für VBA. Eigentlich sogar zwei, denn die eine ist „nur“ eine Prozedur, die zweite eine Funktion. Beide liefern das gewünschte Ergebnis. Die Prozedur sollten Sie in VBA-Editor  in dem Blatt einfügen, wo die Berechnung stattfinden soll. Die Funktion gehört sinnvollerweise in ein allgemeines Modul, damit sie von Prozeduren oder auch direkt innerhalb einer beliebigen Zelle der Mappe aufgerufen werden kann. Innerhalb einer Prozedur könnte der Aufruf dann so aussehen:

LetzteSichtbare = Modul1.LastFilledCell("C")

oder falls Sie den numerischen Wert der Spalte verwenden:

LetzteSichtbare = Modul1.LastFilledCell(3)

Ein Aufruf innerhalb der Arbeitsmappe erwartet nur den Funktionsnamen und als Argument eine gültige Spaltenbezeichnung. Hier nun (auch zum kopieren) den Code für die Prozedur und die Funktion:

Sub LetzteZeileMitInhalt()
   Dim LetzteInhaltZeile As Long
   Dim Col As Range
   
   Application.ScreenUpdating = False
   With ActiveSheet
      Set Col = .Range("A:A")  'Anpassen
      Col.AutoFilter Field:=1, Criteria1:="<>"
      LetzteInhaltZeile = Cells(1, Col.Column).End(xlDown).Row
      Col.AutoFilter
      MsgBox LetzteInhaltZeile
   End With
   Application.ScreenUpdating = True
End Sub
'---------------------------------------------------
Function LastFilledCell(Col As Variant) As Long
   Dim Rc As Long
   On Error GoTo ErrorHandler
   If WorksheetFunction.IsText(Col) Then Col = Columns(Col).Column
   
   Application.ScreenUpdating = False
   With ActiveSheet
      .Columns(Col).AutoFilter Field:=1, Criteria1:="<>"
      Rc = Cells(1, 1).End(xlDown).Row
      Columns(Col).AutoFilter
      LastFilledCell = Rc
   End With

ErrorHandler:
   Application.ScreenUpdating = True
   If Err.Number <> 0 Then
      If Err.Number = 13 Then
         MsgBox "Bitte eine gültige Spaltenbezeichnung" & vbCrLf _
          & "(Zeichen oder Zahl) statt  " & Col & "  eingeben."
      Else
         MsgBox "Fehler Nr.: " & Err.Number & vbCrLf _
          & Err.Description
         LastFilledCell = 0
      End If
   End If
End Function

Sie können diesen Code in dieser *.zip-Datei herunter laden und in Ihr VBA-Projekt einbinden, Änderungen sind selbstredend erlaubt.

Ergänzung: Falls Sie mehrere Spalten auswerten wollen, dann bietet sich dieser Code an; allerdings ohne Fehlerprüfung (kann natürlich „nachgerüstet“ werden):

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

Diese Zeilen stehen Ihnen hier nur per copy/paste zur Verfügung, sie sind nicht in der zip-Datei. Aber das sollte machbar sein …  😉 

▲ nach oben …

Dieser Beitrag wurde unter Mit VBA/Makro, Tabelle und Zelle abgelegt und mit , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.