• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1009
  • Last Modified:

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

0
Cook09
Asked:
Cook09
  • 4
  • 3
1 Solution
 
Rory ArchibaldCommented:
You could simply assign the whole column to a range object then use its count property to get the last cell?
0
 
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.

0
 
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.

Thanks..Cook

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

0
 
Cook09Author Commented:
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?

0
 
Rory ArchibaldCommented:
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
0
 
Cook09Author Commented:
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
0
 
Rory ArchibaldCommented:
You can do it several ways, as with most things in Excel, but there's no need for a separate function here. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now