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 … 😉
[NachObenLetzte Verweis=”T&T: Letzte sichtbare Zelle”]