Solved

How to write a Table Reference in VBA

Posted on 2010-11-23
8
938 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
  • 4
  • 3
8 Comments
 
LVL 85

Expert Comment

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

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Cook09
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now