The Table Address and Structured Data in Excel

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.

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 Editor, only the data range is returned.

? range("table1").address $A$5:$L$42

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 $A$4:$L$43

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 $A$4:$L$42

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 $A$5:$L$42

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.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

MarieApril 12, 2012 at 03:56 PM

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. Thanks!!

Gregoryexcelsemipro.comApril 13, 2012 at 03:30 AM

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
    rng2.CurrentRegion.ClearContents

    'Run Filter
    rng1.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=rngCriteria, _
        CopyToRange:=rng2, _
        Unique:=False
 
    'Select Filter Sheet
    rng2.Parent.Select
End Sub

Excel Table Structured Data

Enjoyed this guide?

Archived comments