View original (French)

Sorting with VBA

Solved
Ecam39 -  
Hello,

I have an Excel table with an undefined (variable) number of rows, and I would like to perform a two-level sort.

The first is in alphabetical order (column B)
The second is also in alphabetical order (column D)

I want to perform this sort on the entire table except for column A, which contains the line numbers. If I include it, I end up with number 15 above number 20, which is not ideal.

Thank you for your responses

Configuration: Windows 7 / Internet Explorer 10.0

3 answers

  1. melanie1324
    Hello,

    here is the vba code:

    sub sort
    i=1
    ' defines the number of rows in your table using variable i
    do while cells(I,1) <> ""
    i=i+1
    loop

    Range(cells(1,2),cells(i,10)).Select 'selects the range from cell B1 to Ji
    ==> J is represented by 10 (if you want to stop before another column, replace 10 with the column number) and i represents the last row of your table

    'For the continuation replace Sheet1 with your sheet name
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(cells(2,2),cells(i,2)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(cells(2,4),cells(i,4)), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range(cells(2,2),cells(i,10))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    end sub
    -1