sort table listobject without table name

O

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
 
C

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.
 
O

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!
 
C

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.
 
O

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top