CODEWITHSUNDEEP
excelvba
Tt H
Tt H

Reputation: 1

How to sort Excel table by its column

I have a table as shown below. How do I sort this table by arranging the columns in ascending date order?

enter image description here

Upvotes: 0

Views: 475

Answers (1)

teylyn
teylyn

Reputation: 35915

You can sort a data range left to right, but you cannot sort a table left to right. You need to convert a table to a range first, then you can apply the sort command with the option to sort left to right. After that, you can make the range into a table again.

The macro recorder will be helpful in establishing the correct code for the left to right sort. Fire it up, then in the sort dialog click "Options" and select "left to right". Select your sort row and finish the dialog. The code then looks along the lines of this:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("A1:D3").Select
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add2 Key:=Range("A1:D1") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("A1:D3")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$3"), , xlYes).Name = _
        "Table2"
    Range("Table2[#All]").Select
End Sub

Adjust to suit your scenario.

Upvotes: 3

Related Questions