Sorting with VBA Solved

Question

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

melanie1324 · Answer

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

Le Pingou · Answer

Hello,
I feel that the line number in [A:A] is completely unnecessary since it has no relation to the rest of the line.
It should be removed, or what is it for....?
The sorting order is summarized by this line:
Columns("A:X").Sort Key1:=Columns("B"), Order1:=xlAscending, Key2:=Columns("C"), Order2:=xlAscending, Header:=xlGuess
The [Columns("A:X").] refers to the columns of the table, to be adjusted as needed.

--
Regards.
The Penguin

Ecam39 · Answer

I was able to sort this out, and for now, it seems to be working :)  Thank you for your responses.  It saves me some hassle^^

Sorting with VBA

3 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.