Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default 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!
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default 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"



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 59
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table sort acss New Users to Excel 2 March 25th 09 05:18 AM
How to save current filter of Table(ListObject) and to apply it la Smugliy Excel Programming 4 November 20th 08 01:34 PM
How do I sort pivot table data outside a pivot table Michael Excel Worksheet Functions 1 January 4th 07 03:45 PM
Table sort hijack Excel Discussion (Misc queries) 4 August 31st 06 04:34 PM
pivot table sort entries that don't yet appear in table rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM


All times are GMT +1. The time now is 07:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"