How to sort Excel Datatable using VBA Code

Feedback Activities
, ,
1

Hi all!

My excel is too big so i can’t use read range and sort datatable activity. I am searching how can i use VBA code ?

I want to sort depend on 2 columns. Like below :
Sort

Do you have any vba code?

3

Hi @Betul_Dundar

Please try this macro:

Sub SortData()
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Set wb = Workbooks.Open("Path to your Excel file")
Set ws = wb.Worksheets("Sheet1")
Set rng = ws.Range("A1").CurrentRegion

With rng
        .Sort Key1:=.Columns(1), Order1:=xlAscending, _
              Key2:=.Columns(2), Order2:=xlAscending, _
              Header:=xlYes
End With

wb.Close SaveChanges:=True
End Sub

Hope this helps,
Best Regards.

4

Thanksc @arjunshenoy but it gives me an error: Invoke VBA: Exception from HRESULT: 0x800A9C68

5

@Betul_Dundar

You basically want to sort both columns in an alphabetical order? Because if column 1 is sorted alphabetically, there are changes that while sorting column 2, the previous sort might get scrambled again?

Modified the macro for the same, kindly give this a try & let us know if this is what you are looking for:

Sub SampleMacro()
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:=Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Hope this helps,
Best Regards.

6

Thanks! It works!

1 Like
7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.