Search code examples
excelvbarangelistobjectheader-row

vba excel listobject HeaderRowRange

I am having some problem to understand how HeaderRowRange works. Lets see: According to the documentation:

enter image description here

headersRowrange is a Range

so this should work:

Dim ftsTbl As ListObject
    Set ftsTbl = ThisWorkbook.Sheets(1).ListObjects(1)

MsgBox ("ftsTbl.HeaderRowRangeD1:" & ftsTbl.HeaderRowRange("D1").Address)

but it doesnt. (invalid procedure call or argument) Why? nor does the following work:

MsgBox ("ftsTbl.HeaderRowRangeD1:" & ftsTbl.HeaderRowRange("D1").item(1).Address)

What I really need to do is getting the following range in this listObject: enter image description here

I need the range of the header of the listobject from D1 to D6 columns. I thought that I could use Range( cell1 ,cellX) like this:

Dim ftsTbl As ListObject
    Set ftsTbl = ThisWorkbook.Sheets(1).ListObjects(1)
Dim DocsHeadersRange As Range
    'Set DocsHeadersRange = ThisWorkbook.Sheets(1).Range(ftsTbl.HeaderRowRange("D1"), ftsTbl.ListColumns("D6").DataBodyRange.iTem(ftsTbl.ListRows))

But it does not work. Why?

I am defining a bunch of ranges in sheet(1) in order to use in 

Sub Worksheet_SelectionChange(ByVal Target As Range)

Set Overlap = Intersect(***defined range of listobject***, Selection)
If Not Overlap Is Nothing Then
If Overlap.Areas.Count = 1 And Selection.Count = Overlap.Count Then
...etc

Thanks Cheers

Solution

  • D1 is already a valid range address so don't name it that if you want to refer to it with any Range object (not sure about your use here as I don't think it is valid anyway. You can use Find. Also, why do you have D1 on the end of ftsTbl?

    Public Sub TEST()
    Dim ftsTbl As ListObject
    Set ftsTbl = ThisWorkbook.Sheets(1).ListObjects(1)
    Debug.Print ftsTbl.HeaderRowRange.Find("D1").Address
End Sub

    You can also use ListColumns:

    Debug.Print ftsTbl.ListColumns("D1").Range.Cells(1, 1).Address

    You can then use something like

    Debug.Print ThisWorkbook.Worksheets("Sheet1").Range(ftsTbl.ListColumns("D1").Range.Cells(1, 1), ftsTbl.ListColumns("D6").Range.Cells(1, 1)).Address

    or

    Debug.Print ThisWorkbook.Worksheets(ftsTbl.Parent.Name).Range(ftsTbl.ListColumns("D1").Range.Cells(1, 1), ftsTbl.ListColumns("D6").Range.Cells(1, 1)).Address