How to return text from visible cells?

Question

I want to create a cell that returns the text from visible cells, to link this cell to a chart title. Functions lijke AGGREGATE and SUBTOTAL returns only numbers from visible cells. Is there a way to return text?

Answer 1

I am likewise unclear what is needed. However, I created this public function to do something like what you requested:

Option Explicit

Public Function ConcatVisibleWithSeparator(rngRange As Range, strSeparator As String) As String
    Dim rngCell As Range
    Dim strReturn As String
    For Each rngCell In rngRange
        If rngCell.EntireRow.Hidden = False Then
            strReturn = strReturn & rngCell.Value & strSeparator
        End If
    Next rngCell
    ConcatVisibleWithSeparator = Left(strReturn, Len(strReturn) - Len(strSeparator))
End Function

It is called with an entry in a cell like this:

=ConcatVisibleWithSeparator(B2:B7," ")

What it does is concatenate everything in the specified range with the specified separator in between values.

Two examples are attached as visuals.

Here:

and here: