sort table listobject without table name

[oldyork90]

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

 

[Claus Busch]

Hi,

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

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.

 

[oldyork90]

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!

 

[Claus Busch]

Hi,

Am Mon, 24 Feb 2014 10:24:15 -0800 (PST) schrieb (e-mail address removed):

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.

 

[oldyork90]

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"

 

[oldyork90]

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

http://www.msofficeforums.com/excel-programming/17988-vba-sort-table-code.html

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