How to write a Table Reference in VBA

In Excel 2007, the program has greatly expanded the capability of Tables.  There have a number of articles and snippets of code that reference Table columns and if the row happens to be known, the possibility of using [#This Row].  I haven't yet seen something that references a Table Column(#Header] Name with the lLastRow of the Table.

Is there a way of accurately forming a statement that can incorporate both pieces of a Table as a reliable reference point that can be used within VBA?  This would be without having to run a separate lLastRow statement, and then using that to somehow piece together the cell reference of the lLastRow and Table Column Name?  Somehow it would seem there should be a:

Table[[LastRow]:[Column2]] ,

method of finding the cell reference at that intersection.

Thanks, Cook

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
Because it's returning the Address property; you can replace that with whatever you need.

Sorry to hear of your loss too. Remember, there's no rush on this so take care of the personal things first.

Rory ArchibaldCommented:
You could simply assign the whole column to a range object then use its count property to get the last cell?
NorieVBA ExpertCommented:
You could  created a reference to the table using ListObject and through that you can access all the elements
of it eg headers, data, columns, rows etc.

There are properties like DataBodyRange, ListColumns, ListRows, HeaderRows etc all of which you can return a range object from.

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Cook09Author Commented:
Sorry, It has taken so long to respond, but with the Holiday's and a death in the family, I just know got back for a couple of hours before having to head out again.

Yes, I can find the last row within a Table or the Column on a sheet.  Normally, it's  using a Function, which is labeled  lLastDataRow.  But, the syntax of using that last row variable number, lLastDataRow, with the Table Header Name is what I'm trying to find.  

 In other words, this:

 TargetCell = TableName[[TableLastRow]:[ColumnHeader]], or Range("TableName[# This Row]:[ColumnHeader]"), or whatever the correct method of writing this type of VBA code.

 If I use [# This Row] an error keeps occurring. Normally it's looking for an end of statement, but other errors have also occurred.   I'd like to be able to use the Column Header Reference, so the initial column is not hard coded.  Therefore, which is now occurring, I add a column, and the result is  having to search the code for the hard coded reference.  As you can imagine, this is a lot more time consuming than the program automatically adjusting itself based upon having a  Table column heading name.  I know most still don't use 2007-2010 Table syntax's, but I like to give it a shot.

What I found in Pearson's Write-up, addresses most of the combination's, exception the intersection between the Last Row and the Column Header Name.


Rory ArchibaldCommented:
There is no last row identifier, which is why I suggested something like:

With ActiveSheet.Range("Table1[header_name]")
    MsgBox .Cells(.Cells.Count).Address
End With

Open in new window

Cook09Author Commented:
Do you know why this is considered a String and not a Range or Numeric (Long).  I'll need to test, but if I wanted to use it like TargetCell.Offset(0,3), would I still  be able to do that, or do I have to create another With...End With statement?

Cook09Author Commented:
After today, the Last Row with Table Column was revisited.  Given a Table "Log_File" that has as one of its Headings "Project_ID", then setting ACell (As Range) would be:

Set ACell = Cells(lLastRow, Range("Log_File[Project_ID]").Column)

Just thought you might be interested.

Rory ArchibaldCommented:
You can do it several ways, as with most things in Excel, but there's no need for a separate function here. :)
All Courses

From novice to tech pro — start learning today.