obtain the data in the first filtered cell

Question

given data in excel sheet that contain a title cell. the perpes is to change the value of the title cell automaticly to the value of filtering.

| titl cell | |

Col A	Col B
A	pen
B	tree
C	pen
D	cat

so if we filtered the Col B based on the value "pen" we will get the result as follow:

| pen | |

Col A	Col B
A	pen
C	pen

Ps: the title cell change based on the filtered cells not the other way.

any hit please?

i tried th VBA but it was time cansuming more then copy and post after each tima i do the filter

Answer 1

You can do it using a custom function. the input for this function should be the whole range of the part of the data that has the names in it, before you filtered it. The output is the value2 of the first visible cell in the filtered range.

Option Explicit

    Function IsVisible(rg As Range) As String
    Dim rgvis As Range

    Set rgvis = rg.SpecialCells(xlCellTypeVisible)

    IsVisible = rgvis.Cells(1, 1).Value2

    End Function