VBA Blogs: Header-ache

The sixth in a series about using ListObjects to manipulate Tables within an Excel workbook in VBA featuring the Headers Range.

Let’s not get ahead of ourselves and examine our table.

Notice how our table has no header names? Excel has just given it the generic Column + # header name. How would one fix this in VBA? No need to bury your head in the sand, it’s no head-scratcher.

Go head-to-head with the HeaderRowRange property of the ListObject. This can be used to retrieve the range of the header row.

With a cool head, this then can be accessed directly to change the name of the headers.  The Cells property can be used.  Remember off the top of your head that Cells uses ([RowNumber] , [ColumnNumber]) references – the column ordinality of the table will definite which header is changed.

Let’s run the following code and head toward a more meaningfully named table:

Heads up! This is the result:

Success! But heads will roll if this table was sent out - “Drug Name” is a misnomer, using “Brand” name would be more accurate. If the column number is unknown, how does one rename the column? How do we make heads or tails about that?

Don’t knock your head against a brick wall, use the Find method of Range to find the cell address of a particular name in the Header list and then the column position. Then reference the cell directly in order to change the header.

And tada! Hold your head up high to a correctly named table:

Working on tables head to toe, next week will be about the Totals Row on the other end of the Table.