How to return text from visible cells?
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?
Without any type of example, I can only offer the use of the & ...
="This is "&C15
where cell C15 contains the text "a test"
Or if B15 contains "This is " then:
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:
What it does is concatenate everything in the specified range with the specified separator in between values.
Two examples are attached as visuals.