Test for empty Listobject header

Question

How to test if an Excel (Version >= 2010) ListObject header is empty?

If I select such an empty header, then

?selection.value -> Column1 (or Spalte1, depending on client language)
?isEmpty(selection) -> false

So as far as VBA is concerned, it behaves like a non-empty cell.

Non-solution: Writing to the cell and then check if its value changed.

Answer 1

We settled with this solution. Somewhere on the workbook insert a ListObject (aka Table) with one column, leaving the header empty. Give this header the name emptyHeader.

With the function below you can now check if a given range is really empty, that is has empty cell value or a value auto-set by the ListObject for empty headers.

The solution is not perfect. If a user sets the header value of the second column to Column42 (in case of english), then the function will erroneously return true.

Function isRealyEmpty(ra As Range) As Boolean
    If IsEmpty(ra.Value) Then
        isRealyEmpty= True
        Exit Function
    End If

    ' The value of the empty reference header
    emptyHeaderValue = ThisWorkbook.Names.Item("emptyHeader").RefersToRange.Value
    l = Len(emptyHeaderValue) - 1

    targetValue = ra.Value

    If Left(emptyHeaderValue, l) = Left(targetValue, l) Then
        postFix = Right(targetValue, Len(targetValue) - l)
        isRealyEmpty= IsNumeric(postFix)
    Else
        isRealyEmpty= False
    End If

End Function