'Check if a cell from a selected range is visible

I have a VBA function in Excel returns a concatenated string of text from cells selected by users.

This works as I require, however if there are hidden cells in the selection, the value of the hidden cell is included, which is undesirable. An example of when this issue occurs is when a table is filtered.

Is there a way to amend my function to check if the cell that is being read is visible?

Sub ConcatEmialAddresses()

    Dim EmailAddresses As String

    ActiveSheet.Range("C3").Value = combineSelected()
    ActiveSheet.Range("C3").Select

    Call MsgBox("The email address string from cell ""C3"" has been copied to your clipboard.", vbOKOnly, "Sit back, relax, it's all been taken care of...")

End Sub

Function combineSelected(Optional ByVal separator As String = "; ", _
                         Optional ByVal copyText As Boolean = True) As String

    Dim cellValue As Range
    Dim outputText As String

    For Each cellValue In Selection
        outputText = outputText & cellValue & separator
    Next cellValue

    If Right(outputText, 2) = separator Then outputText = Left(outputText, Len(outputText) - 2)

    combineSelected = outputText

End Function

Solution 1:^[1]

To determine if a Range has an hidden cell, I would check that the height/width of each row/column is different from zero:

Function HasHiddenCell(source As Range) As Boolean
  Dim rg As Range

  'check the columns
  If VBA.IsNull(source.ColumnWidth) Then
    For Each rg In source.Columns
      If rg.ColumnWidth = 0 Then
        HasHiddenCell = True
        Exit Function
      End If
    Next
  End If

  ' check the rows
  If VBA.IsNull(source.RowHeight) Then
    For Each rg In source.rows
      If rg.RowHeight = 0 Then
        HasHiddenCell = True
        Exit Function
      End If
    Next
  End If
End Function

Sub UsageExample()
  If HasHiddenCell(selection) Then
    Debug.Print "A cell is hidden"
  Else
    Debug.Print "all cells are visible"
  End If
End Sub

Solution 2:^[2]

I used this

Function areCellsHidden(Target As Range)
    areCellsHidden = False
    If (Target.Rows.Hidden = True) Then
        areCellsHidden = True
    ElseIf (Target.Columns.Hidden = True) Then
        areCellsHidden = True
    ElseIf (Target.Count > 1) Then
        If _
            Target.Count <> Target.Columns.SpecialCells(xlCellTypeVisible).Count _
            Or Target.Count <> Target.Rows.SpecialCells(xlCellTypeVisible).Count _
        Then
            areCellsHidden = True
        End If
   End If
End Function

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Florent B.
Solution 2

'Check if a cell from a selected range is visible

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]