How do you convert a structured reference in excel to a range?

Question

I have a table with columns that have headers. I'd like to be able to convert the structured reference [Column 2] to the range B1:B1000 and Table1[[#Data],[Column2]] to the range B2:B1000.

edit for context:

This is in the formula editor so the structured references refer to a table in the current worksheet.

The Structured ref comes from a table in the formula editor, but VBA is fine — nimish, Aug 28 '12 at 14:32

score 4 · Accepted Answer · answered Aug 27 '12 at 06:50

4

This will give you the address:

Activesheet.Range("Table1[[#Data],[Column2]]").Address()

This would give you a reference to the range itself

Dim rng As Range
Set rng = Activesheet.Range("Table1[[#Data],[Column2]]")

answered Aug 27 '12 at 06:50

Tim Williams

154,628
8
97
125

score 1 · Answer 2 · answered Aug 27 '12 at 08:19

In the Table options you can select the option Convert to Range which will change structured references in formulas to range references, you can then choose Insert Table to reinstate the table. To simplify formulas you could use find & replace e.g. Sheet1! and $ with blank.

How do you convert a structured reference in excel to a range?

2 Answers2

Linked