Solved

How to write a Table Reference in VBA

Posted on 2010-11-23
8
962 Views
Last Modified: 2012-05-10
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
Comment
Question by:Cook09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34196165
You could simply assign the whole column to a range object then use its count property to get the last cell?
0
 
LVL 34

Expert Comment

by:Norie
ID: 34207935
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
 

Author Comment

by:Cook09
ID: 34231902
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34231994
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
 

Author Comment

by:Cook09
ID: 34232225
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 34232395
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
 

Author Comment

by:Cook09
ID: 34263547
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34264875
You can do it several ways, as with most things in Excel, but there's no need for a separate function here. :)
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question