Erweiterte Methoden zum Ermitteln des letzten Werts in Excel-Zellbereichen

In meiner täglichen Arbeit mit Excel stieß ich auf eine häufige Herausforderung: Wie ermittelt man effizient den letzten Wert eines Zellbereichs, der bestimmte Kriterien erfüllt? Die gängige Frank-Kabel-Lösung mittels Excel-Formel ist ein guter Ansatz, aber sie hat ihre Grenzen, besonders wenn man Werte ausschließen möchte, die sowohl 0 als auch leer (“”) sind. Als VBA-Entwickler suchte ich nach flexibleren Lösungen, die verschiedene Anforderungen erfüllen können.

Ermitteln mittels Excel-Formel

Um den letzten Wert eines Zellbereiches der > 0 ist zu ermitteln, kann man die bekannte Frank-Kabel-Lösung verwenden.

> =VERWEIS(2;1/(ZELLBEREICH<>0);ZELLBEREICH)

bzw. für Werte ungleich “”

> =VERWEIS(2;1/(ZELLBEREICH<>””);ZELLBEREICH)

Das ganze hat jedoch folgende Einschränkung – man kann nicht festlegen, dass Zellbereich <> 0 und Zellbereich <> “” ausgewertet wird. (Jedenfalls habe ich noch keine Lösung dafür gefunden)

Ermitteln mittels VBA

	' Funktion, um den letzten Wert in einem Zellbereich zu finden, der nicht Null und nicht leer ist
	Function letzterWert(myRange As Range) As Double
	' Deklariert eine Variable für die Durchlauf-Elemente in myRange
	Dim element As Range

	' Durchläuft jedes Element in dem übergebenen Zellbereich
	For Each element In myRange
	' Überprüft, ob der Wert des Elements weder Null noch leer ist
	If element.Value <> 0 And element.Value <> "" Then
	' Gibt die Adresse und den Wert des aktuellen Elements in der Direktfenster-Ausgabe aus
	Debug.Print element.AddressLocal & " - " & element.Value

	' Setzt den Wert des aktuellen Elements als Rückgabewert der Funktion
	letzterWert = element.Value
	End If
	Next
	End Function

view raw letzterWert.vba hosted with

by GitHub

Bei einzelnen Spalten oder Zeilen ist die Vorgehensweise des Skripts logisch (von oben nach unten, von links nach recht). Aber wie sieht es bei Zellbereichen aus, die sowohl mehrer Zeilen als auch mehrere Spalten haben.

Das mag zwar praktisch sein, wirklich viel damit machen kann man aber noch nicht. Wie sieht es aus, wenn man statt horizontal vertikal auslesen möchte? Was ist wenn man den den 4.letzten Wert haben möchte usw.

Ausgangslage ist folgender Bereich:

Mit der erweiterten Formel kann man dann den Bereich auch folgendermaßen auslesen:

erweiterte Formel

	Option Explicit

	' Funktion, um den letzten oder n-letzten Wert in einem Zellbereich zu finden
	Function letzterWert_Extended(myRange As Range, Optional how As Integer = 1, _
	Optional ignoreError As Boolean = True, _
	Optional ignoreEmptyCells As Boolean = True, _
	Optional ignorenull As Boolean = False, _
	Optional Stelle As Integer = 1) As Variant
	Dim i As Integer, j As Integer, counter As Integer
	Dim myArray() As Variant
	Dim bIgnore As Boolean
	Dim returnValue As Variant

	' Entscheidet, ob horizontal (1) oder vertikal (2) durchsucht werden soll
	If how = 1 Or how = 2 Then
	For i = 1 To IIf(how = 1, myRange.Rows.Count, myRange.Columns.Count)
	For j = 1 To IIf(how = 1, myRange.Columns.Count, myRange.Rows.Count)
	' Prüft auf leere Zellen, Fehler oder Nullwerte, um sie ggf. zu ignorieren
	bIgnore = False
	If IsEmpty(myRange.Cells(IIf(how = 1, i, j), IIf(how = 1, j, i)).Value) And ignoreEmptyCells Then
	bIgnore = True
	End If
	If IsError(myRange.Cells(IIf(how = 1, i, j), IIf(how = 1, j, i)).Value) And ignoreError Then
	bIgnore = True
	End If
	If IsNumeric(myRange.Cells(IIf(how = 1, i, j), IIf(how = 1, j, i)).Value) Then
	If (myRange.Cells(IIf(how = 1, i, j), IIf(how = 1, j, i)).Value = 0) And ignorenull Then
	bIgnore = True
	End If
	End If
	' Speichert den Wert, wenn er nicht ignoriert wird
	If Not bIgnore Then
	ReDim Preserve myArray(counter)
	myArray(counter) = myRange.Cells(IIf(how = 1, i, j), IIf(how = 1, j, i)).Value
	returnValue = myArray(counter)
	counter = counter + 1
	End If
	Next
	Next
	Else
	returnValue = CVErr(xlErrNA)
	End If

	' Gibt den letzten oder den n-letzten Wert zurück
	If Stelle = 1 Then
	letzterWert = returnValue
	Else
	If counter >= Stelle Then
	letzterWert = myArray(counter - Stelle)
	Else
	letzterWert = CVErr(xlErrNA)
	End If
	End If
	End Function