View original (French)

[VBA] Sort an Excel array using VBA

tzehani Posted messages 11 Status Membre -  
 titounette -
Hello everyone, I would like to automate sorting with VBA code rather than doing it manually.

For example: I have an Excel table with 3 columns and I would like to sort the table by column B while keeping the rows.
That is to say, for example:

red tomato vegetable
green grape fruit
black spider animal

would become

black spider animal
green grape fruit
red tomato vegetable

Thank you very much for your valuable help. 
Configuration: Windows XP Internet Explorer 6.0

2 réponses

Réponse 1 / 2
Papou93 Posted messages 146 Registration date   Status Membre Last intervention   59
 
Hello tzehani,

Here is a very simple macro to run after selecting the area to sort: 

Sub TriPerso() Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub


or the same one that selects a fixed area: 

Sub TriPerso() Range("A1:C3").Select Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub


Tip: it’s very easy to achieve such simple actions by running the macro recorder.

Best regards.
9
tzehani Posted messages 11 Status Membre
 
Thank you very much for the advice, I will take your second proposal however I need A1:ANi and it is this notation that I cannot code, here is my code :

Sub TriPerso()

Do While Range("A" & i) <> 0
i = i + 1
Loop

Range("A1:AN" & i).Select =====> Problem with the expression AN&i


Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


Thank you again for your attention
0
Sir_DEC Posted messages 143 Status Membre 75 > tzehani Posted messages 11 Status Membre
 
Hello,

You made a typing error: Range("A1:AN & i") => Range("A1:AN" & i) or Range("A1:A" & i)
0