vba excel listobject HeaderRowRange

Question

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

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:

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

score 1 · Accepted Answer · answered Dec 13 '18 at 08:46

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

Hi Qharr, thanks a lot. Not only you provide me with the solution but you also spot that D1 is a cell so it might cause me trouble as name. I will try to change it by Doc1. Probably too difficult since I have a bunch of code... fts.parent.name is also phantastic, I did not know it. btw, is there something like "parent sheet" when working in Worksheet_SelectionChange(ByVal Target As Range) ??? — JFerro, Dec 13 '18 at 09:12

score 0 · Answer 2 · answered Dec 13 '18 at 09:15

The solution post by @QHarr solves the problem: Besides it is possible to build the range from D1 till the end of the listobject in this way:

Dim DocsHeadersRange As Range
Set DocsHeaders
Range = ThisWorkbook.Sheets(1).Range(ftsTbl.ListColumns("D1").Range.Cells(1, 1), ftsTbl.ListColumns(ftsTbl.ListColumns.Count).Range.Cells(1, 1))
MsgBox DocsHeadersRange.Address

vba excel listobject HeaderRowRange

2 Answers2