Sorting a dynamic table in vba

QuestionEdit

Using2003, I have a table in which the range (named "Counts_Table") is dynamically defined based on a formula as follows:

=OFFSET(Counts!$A1:$A$2,0,0,COUNTA(Counts!$A:$A)-1,13)

The table has a header row (row #1), but this header is not included in the range definition above.

While executing a macro in Excel, I have included the following sort statement:

       Range("Counts_Table").Sort Key1:=Range("Counts_Table").Cells(Counts_Exec_Name_Col, Counts_Start_Row), Order1:=xlAscending, _
           Key2:=Range("Counts_Table").Cells(Counts_Priority_Data_Col, Counts_Start_Row), Order2:=xlAscending, _
           Key3:=Range("Counts_Table").Cells(Counts_Rule_ID_Col, Counts_Start_Row), Order3:=xlAscending, _
           Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
           xlTopToBotDataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
           DataOption3:=xlSortTextAsNumbers

For the above, the following constants have been set:

Counts_Start_Row = 2 (this is the first row of actual data to be sorted) Counts_Exec_Name_Col = 3 (column C) Counts_Priority_Data_Col = 13 (column M) Counts_Rule_ID_Col = 1 (column A)

The result of this sort is not producing the expected result of having Count_Table sorted by column C in ascending order, column M in ascending order and column A in ascending order.

Can you diagnose the problem with my sort statement?

AnswerEdit

Dave,

a couple of problems

Your formula =OFFSET(Counts!$A1:$A$2,0,0,COUNTA(Counts!$A:$A)-1,13) could be faulty since the relative address 1 in $A1 could be interpreted differently based on where the activecell is located. I used this formula

=OFFSET(Counts!$A$2,0,0,COUNTA(Counts!$A:$A)-1,13)

to define the name Counts_Table and it consistently defined the correct range.

the second major problem is you have your arguments reversed for the Cells object. The arguments are Cells(row, column) and you are using Cells(column, row)

I made the above adjustments and the code worked fine for me.

Sub ABC() Dim Counts_Start_Row As Long Dim Counts_Exec_Name As Long Dim Counts_Priority_Data_Col As Long Dim Counts_Rule_ID_Col As Long Dim r As Range Counts_Start_Row = 2 Counts_Exec_Name_Col = 3 ' (column C) Counts_Priority_Data_Col = 13 '(column M) Counts_Rule_ID_Col = 1 '(column A)

Set r = ThisWorkbook.Names("Counts_Table").RefersToRange Debug.Print r.Address r.Sort Key1:=r.Cells(Counts_Start_Row, Counts_Exec_Name_Col), Order1:=xlAscending, _

   Key2:=r.Cells(Counts_Start_Row, Counts_Priority_Data_Col), Order2:=xlAscending, _
   Key3:=r.Cells(Counts_Start_Row, Counts_Rule_ID_Col), Order3:=xlAscending, _
   Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:= _
   xlTopToBotDataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
   DataOption3:=xlSortTextAsNumbers

End Sub

I added the variable r rather than Range("Counts_Table") just for convenience.