sort table listobject without table name
 

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

sort table listobject without table name
 

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

sort table listobject without table name
 

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!

sort table listobject without table name
 

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

sort table listobject without table name
 

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"

sort table listobject without table 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