Erster sichtbarer Text aus gefilterter Spalte

Question

Guten Tag, wie müsste ein vba lauten in Spalte E1 ist ein Filter gesetzt es soll in Spalte E308 immer der Text stehen der als erster in der gefilterten Spalte ab E1 (nach dem Filter) aufscheint. Danke

Answer 1

Hallo Jelena,

Das würde mit folgendem Code in einem normalen Modul funktionieren:

Function ErsterFilterwert(r As Range)
   Application.Volatile
   Dim c As Range
   With r.Parent
     For Each c In .Range(.Cells(.AutoFilter.Range.Row, r.Column), .Cells(.AutoFilter.Range.Row + .AutoFilter.Range.Rows.Count - 1, r.Column))
       If Not c.EntireRow.Hidden And c.Row <> .AutoFilter.Range.Row Then
         ErsterFilterwert = c
         Exit For
       End If
     Next c
   End With
End Function

In Zelle E308 schreibst du dann einfach die Formel =ErsterFilterwert(E1)

Gruß Mr. K.

Answer 2

Hallo Mr. K. Danke

Answer 3

Hallo Mr. K. wenn kein Filter aktiv dann erscheint in Zelle E308 der Text #WERT! wie kann man das verhindern. Danke

Answer 4

Hi Jelena,

das geht auch ohne VBA mit folgender Formel: {=WENN(TEILERGEBNIS(3;A2:E18)-ANZAHL2(A2:E18)=0;"";INDEX(E:E;MAX(TEILERGEBNIS(3;INDIREKT("E"&ZEILE(2:18)))*ZEILE(2:18))))}

Du musst E18 natürlich durch die Adresse der letzten Zelle der letzen Spalte, also unten rechts, deines Autofilterbereichs ersetzen sowie bei ZEILE(2:18) die 18 durch die letzte Zeilennummer.

Beachte bitte auch, dass dies eine Matrixformel ist - du musst die Eingabe mit der Tastenkombination Strg+Shift+Enter abschließen.

Ursprung dieser Formellösung siehe hier: https://www.herber.de/excelformeln/pages/Autofilter_Ueberpruefung_ob_und_wonach_eine_Liste_gefiltert_ist.html

Bis später, Karin

Answer 5

Hallo Karin, möchte aber die vba lösung. Danke

Answer 6

Interessant, die Lösung von Karin kannte ich noch nicht. Funktioniert auch ganz gut. Gibt allerdings nicht den ersten sondern den letzten Wert der gefilterten Liste zurück. Außerdem muss man dann den genauen Bereich kennen, worauf sich der Autofilter bezieht. Sobald dieser sich ändert muss man die Formel anpassen.

@Jelena. Warum schaltest du den Autofilter komplett aus? Logisch dass, dann nichts gefunden wird. Wenn dich die Fehlermeldung stört, dann mach doch einfach ein Wennfehler drumrum: =Wennfehler(ErsterFilterwert(E1);"") oder alternativ =Wennfehler(ErsterFilterwert(E1);E2)

Gruß Mr. K.

Answer 7

Hallo Mr. K. die Spalte E geht von E2 bis E308 jedoch sind nicht alle Zellen mit inhalt. Danke

Answer 8

Moment mal, In E308 steht doch deine Formel. Also muss die Datenliste spätestens in E307 enden. Wenn nicht alle Zellen mit Inhalt sind, dann musst du den Bereich von A1 bis E307 vorher markieren und erst dann den Autofilter setzen. Wenn der erste Filterwert eine Leerzelle ist dann wird 0 zurückgegeben. Falls du die erste nichtleere Zelle zurückgeben willst (also z.B. die zweite filterzeile) dann ersetze Die die eine Zeile wie folgt:

If Not c.EntireRow.Hidden And c.Row <> .AutoFilter.Range.Row And c <> "" Then

Gruß Mr. K.

Answer 9

Hallo Mr. K. vielen Dank