Indirect function referencing table header row

Question

Im trying to have an indirect function in my range select to take the headers of a specific table.

Sub Makro1()

    Sheets("Data").Select
    Range("XXXX[#Headers]").Select
    
    ActiveWorkbook.Names.Add Name:="Labelname", RefersToR1C1:="=XXXX[#Headers]"
    ActiveWorkbook.Names("Labename").Comment = ""
    
End Sub

Lets assume I have the right name for the table in the worksheet "SH1" in the Cell A1

My initial idea was:

Range("[indirect("SH1!A1")]&[#Headers]").Select

Not really getting what you mean. The formula `=INDIRECT($A$1 & "[#Headers]")` will return the headers for the named table. In VBA you can use `ListObject` to reference a table: `Dim lo As ListObject:Set lo = ThisWorkbook.Worksheets("Data").ListObjects("Table1")` and return the header range by using `lo.HeaderRowRange` — Darren Bartrup-Cook, Nov 11 '21 at 14:23
You already defined the name of the tables saying "ListObjects("Table1")" but rather than defining the name of the table in the code it has to look for the name in the cell SH1!A1 — Ken Kaneki, Nov 11 '21 at 14:34
You mean `Range(Worksheets("SH1").Range("A1").Value & "[#Headers]").Select`? — Jos Woolley, Nov 11 '21 at 14:43
@JosWoolley thank you very much! You helped me a lot. One last thing, how do I put the selected header row in a label name? — Ken Kaneki, Nov 11 '21 at 15:04
@JosWoolley No just a simple Name in the Name manager for the selected range in the header — Ken Kaneki, Nov 11 '21 at 15:46
`Dim MyRange As Range With ThisWorkbook Set MyRange = Range(.Worksheets("SH1").Range("A1").Value & "[#Headers]") .Names.Add Name:="NewNamedRange", RefersTo:=MyRange End With` — Jos Woolley, Nov 11 '21 at 15:56

score 0 · Answer 1 · answered Nov 11 '21 at 14:54

If you want to get the table name from a cell you can use:

Sub TableHeaders()

    Dim TableName As String
    TableName = ThisWorkbook.Worksheets("Data").Range("A1")

    Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets("Data").ListObjects(TableName)
    
    ThisWorkbook.Names.Add Name:="LabelName", RefersTo:=lo.HeaderRowRange
    ThisWorkbook.Names("LabelName").Comment = "References " & lo.HeaderRowRange.Address
    
End Sub

Using formula only you could use: =INDIRECT($A$1 & "[#Headers]")

You could create a validation list in cell A1 to list all the tables:

Sub CreateValidationList()

    Dim lo As ListObject
    Dim ValidationList As String
    
    With ThisWorkbook.Worksheets("Data")
        For Each lo In .ListObjects
            ValidationList = ValidationList & lo.Name & ","
        Next lo
        ValidationList = Left(ValidationList, Len(ValidationList) - 1)
    
        With .Range("A1").Validation
            .Delete
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertStop, _
                 Formula1:=ValidationList
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    
    End With

End Sub

Not sure why you'd want to select the table though - you can reference it without selecting it.
How to avoid using Select in Excel VBA

Indirect function referencing table header row

1 Answers1