The Table Address and Structured Data in Excel

by Gregory on April 19, 2011

The data in an Excel Table is nicely addressed. Meaning when you ask Excel for the address of the Table you get the data only and not the Header row or, if toggled, the Total Row.

Excel Table Data Address

The Table above starts in row 4 of the worksheet with a Header and ends in row 43 because the Total Row is toggled on. If I execute the following code inside the Immediate window of the VBA Editoronly the data range is returned.

? range("table1").address

The first line is the VBA code asking for the address of the Table1 range. The second line gives the requested range address.

When I select a cell inside the Table and ask for the range address of the current region, I get the data range, Header, and Total Row.

? selection.currentregion.address

If I toggle off the Total Row and run the same code the data and header is included in the current region.

? selection.currentregion.address

If you want to grab data only with VBA code and aren’t using a Table the following code will work. (In the Immediate window the code has to be on one line, so these two lines below are supposed to be one solid line of VBA code.)
? selection.currentregion.offset(1,0).resize(selection.currentregion.rows.count - 1,selection.currentregion.columns.count).address

This code selects the current region, offsets the entire range one row down, then resizes to one less row at the bottom.

Which is why an Excel Table is nicely addressed.

Excel Table Structured Data

Besides showing the column references in a Table, formula autocomplete shows us other structured data references.

Structured Table References

In the formula above, when I type an open square brace [ after the Table1 name, the formula autocomplete list appears and reveals that #All, #Data, #Headers, #Totals, and @ are available to be used as a reference to different parts of the Table.

The formula =ROWS(Table1) is the same as =ROWS(Table1[#Data]). And the Table1[#All] is equivalent to CurrentRegion.

Related Posts Plugin for WordPress, Blogger...
Marie April 12, 2012 at 8:56 am

What if I did want to include the table headers in the selection? I’m trying to do an advanced filter on a table and copy the results into another sheet, and it needs the header row, otherwise it returns the “The extract range has a missing or illegal field name” error. I want to use the table reference because if I add something to the end of the table, I want it to be included when I filter without having to change it each time. Any thoughts? This is the closest thing I’ve found regarding this topic.


Gregory April 12, 2012 at 8:30 pm

I wrote an article about this: Copy Data to Another Worksheet with Advanced Filter, but when you use a Table the Advanced Auto Filter doesn’t work.

So I re-wrote the VBA code to work with the associated Excel file. Here’s the code:

Sub FilterCopyToOtherSheet()
Dim rng1 As Range
Dim rng2 As Range
Dim rngCriteria As Range

Set rng1 = Sheet1.Range("Table1[#All]")
Set rng2 = Sheet2.Cells(1, 1)
Set rngCriteria = Sheet1.Range("E1:E2")

' Clear old filter range

'Run Filter
rng1.AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=rngCriteria, _
CopyToRange:=rng2, _

'Select Filter Sheet
End Sub

Comments on this entry are closed.

Previous post:

Next post: