Link to home
Start Free TrialLog in
Avatar of Cook09
Cook09Flag for United States of America

asked on

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

Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

You could simply assign the whole column to a range object then use its count property to get the last cell?
Avatar of Norie
Norie

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.

Avatar of Cook09

ASKER

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.

Thanks..Cook

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

Avatar of Cook09

ASKER

Rorya,
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?

ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cook09

ASKER

Rory,
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.

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