HeaderRowRange

[oldyork90]

I am trying to iterate thru column headers in a 2010 table

Dim oWs As Worksheet: Set oWs = Application.ActiveSheet
Dim oLO_table As ListObject: Set oLO_table = oWs.ListObjects(TABLE_ACTUALS)
Dim oRg_header_row As Range: Set oRg_header_row = oLO_table.HeaderRowRange
Dim num_columns As Long: num_columns = oRg_header_row.Columns.Count

Then I try and view all the names in a loop


For ndx = 0 To num_columns - 1
title = oRg_header_row.Offset(0, ndx).value
...
next

I get nothing but nulls. Table is there, I can see it in the watch window.
column count is correct. Where is my mistake?

thank you

 

[Claus Busch]

Hi,

Am Fri, 21 Feb 2014 12:51:26 -0800 (PST) schrieb (e-mail address removed):

Dim oWs As Worksheet: Set oWs = Application.ActiveSheet
Dim oLO_table As ListObject: Set oLO_table = oWs.ListObjects(TABLE_ACTUALS)

here I get an error. It must be:
Dim oLO_table As ListObject: Set oLO_table =
oWs.ListObjects("TABLE_ACTUALS")

For ndx = 0 To num_columns - 1
title = oRg_header_row.Offset(0, ndx).value
...
next

In this case title is an array and it is filled with the first loop.
With every new loop you drag the array to the right and so you get an
empty array.
You don't have to loop through the header row. That is enough:
Title = oRg_header_row
Into the watch window you see the array with all headers.

If you want to show the titles one by one try:

For ndx = 1 To num_columns
Title = oRg_header_row.Cells(ndx)
Next


Regards
Claus B.

 

[oldyork90]

For ndx = 1 To num_columns

Title = oRg_header_row.Cells(ndx)

Next

Thanks again Mr. Busch. These new, to me, objects can be very confusing.