Sorting a table ListObject

The example I found used hard coded names. I want to know if I can avoid this. The only information I want to supply is the name of the column. I already have the table ListObject so I would think it could be done.

Dim oWs As Worksheet: Set oWs = ActiveSheet
Dim oLO_table As ListObject: Set oLO_table = oWs.ListObjects(TABLE_METRIC)

No:

oLO_ma.Sort.SortFields.Clear
oLO_table .SortFields.Add _
Key:=Range("Table1[[#All],[Item number]]"), _
SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal

Yes (something like this):

oLO_ma.Sort.SortFields.Clear
oLO_table.SortFields.Add _
Key:=oLO_table.Range.HeaderRowRange(NAME_OF_COLUMN ), _
SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal

Which is wrong. I've tried many ways but can't do it. Help?
(maybe it can't be done... but you'd think)

Thank you

Hi,

Am Mon, 24 Feb 2014 09:17:08 -0800 (PST) schrieb :

Sorting a table ListObject

The example I found used hard coded names. I want to know if I can avoid this. The only information I want to supply is the name of the column. I already have the table ListObject so I would think it could be done.

try:

With ActiveWorkbook.Sheets("Sheet1").ListObjects("TABLE _METRIC")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("TABLE_METRIC[Value3]"),
Order:=xlAscending
With .Sort
.Apply
End With
End With

Change [Value3] to your header caption


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

With ActiveWorkbook.Sheets("Sheet1").ListObjects("TABLE _METRIC")

.Sort.SortFields.Clear

.Sort.SortFields.Add Key:=Range("TABLE_METRIC[Value3]"),


Well that works but it would be the same as??

s = oLO_table.name & "[" & COL_NAME & "]"


.Sort.SortFields.Add Key:=Range(s)

which is ok, but you shouldn't have to echo data you already know. I'll use your fix until I discover the mystery of the listobject.

Thank you again!

Hi,

Am Mon, 24 Feb 2014 10:24:15 -0800 (PST) schrieb :

Well that works but it would be the same as??

s = oLO_table.name & "[" & COL_NAME & "]"

how did you initialize COL_NAME?

try:
Dim Col_Name As String
Dim s As String

With ActiveWorkbook.Sheets("Sheet1").ListObjects("TABLE _METRIC")
Col_Name = .ListColumns(3).Name
s = .Name & "[" & Col_Name & "]"
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range(s), Order:=xlAscending
With .Sort
.Header = xlYes
.Apply
End With
End With


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

how did you initialize COL_NAME?

I have a "magic" module of constant values. I never use anything but constant values if I can help it. Here is the entry example

Public Const IT_OWNER_COLUMN_NAME_BILLING As String = "Billing Grp Name"

I found it, it's listcolumns().range

http://www.msofficeforums.com/excel-...able-code.html

oLO_ma.Sort.SortFields.Add _
oLO_ma.ListColumns(IT_OWNER_COLUMN_NAME_BILLING).R ange, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal