CODEWITHSUNDEEP
excelvbasharepoint
Damien
Damien

Reputation: 1600

VBA excel table - How to sort based on ColorIndex

I am importing data from sharepoint as an excel table. After importing the data, I highlight some of the rows based on specific criteria. I try to sort the excel table based on colorIndex using vba code, but I am not successful because the table name keeps changing each time the data is imported from sharepoint. Do you have any suggestions for me?

Upvotes: 0

Views: 951

Answers (1)

Damien
Damien

Reputation: 1600

I resolved my issue with this code.

Sub SortTable()
Dim lo As Excel.ListObject

With ActiveSheet
     .ListObjects(1).Name = "MyTableName"
End With

Set lo = ActiveWorkbook.Worksheets("Alpha").ListObjects("MyTableName")
With lo
     .Sort.SortFields.Clear
     .Sort.SortFields.Add(Range("MyTableName[LastName]"), _
     xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.ColorIndex = 4
With .Sort
     .Header = xlYes
     .MatchCase = False
     .Orientation = xlTopToBottom
     .SortMethod = xlPinYin
    .Apply
    End With
End With

End Sub

Upvotes: 1

Related Questions