Sort a column in an excel table using table index vba

Question

How do I reference a specific column within a Table without calling the table by name? I would like to replace ===> Range("Table1[CODER]") <=== with a reference to either the .Listobject index number or using the variable "tableName" that I defined, but I cannot figure out the syntax for either option.

Public Sub CreateTableAndSortYNG()
Dim tableName As String

  With ActiveWorkbook.ActiveSheet
    .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = .Name & "_Table"

tableName = ActiveWorkbook.ActiveSheet.ListObjects(1).DisplayName

MsgBox tableName

    .ListObjects(1).Sort.SortFields.Clear

    .ListObjects(1).Sort.SortFields.Add Key:=Range("Table1[CODER]"), _
     SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .ListObjects(1).Sort.SortFields.Add Key:=Range("Table1[TYPE]"), _
     SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    .ListObjects(1).Sort.SortFields.Add Key:=Range("Table1[DSCHG_DT]"), _
     SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  End With
End Sub

Is CODER going to be what TableName is? You can do .ListObjects(1).Sort.SortFields.Add Key:=Range("Table1[" & tableName & "]"), ... — BruceWayne
CODER is the name of a column within the table. I'm trying to replace the "Table1" portion of the code with an index number or the variable "tableName" since the table will never actually be called "Table1" — Isaac Rothstein
Ah, then I believe you can do .ListObjects(1).Sort.SortFields.Add Key:=Range(tableName & "[CODER]"), _ ... — BruceWayne

BruceWayne BruceWayne · Accepted Answer · 2016-01-14T18:31:25

A couple notes. You're using With, but not taking full advantage of it. Also, you had the theory of how to use a variable in this situation already in place (see With ActiveWorkbook.ActiveSheet.ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = .Name & "_Table").

Here's your code, tightened up:

Public Sub CreateTableAndSortYNG()
Dim tableName As String

With ActiveWorkbook.ActiveSheet
    .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = .Name & "_Table"

    tableName = .ListObjects(1).DisplayName
    'MsgBox tableName ' I removed this because I think you're using this to check the name. I like using the next line instead
    Debug.Print tableName ' This will show in the "Immediate Window" in the VB Editor (press CTRL+G) to see the immediate window
    With .ListObjects(1).Sort.SortFields
        .Clear
        .Add Key:=Range(tableName & "[CODER]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range(tableName & "[TYPE]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range(tableName & "[DSCHG_DT]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
End With
End Sub

Sort a column in an excel table using table index vba

1 Answers