Automatic sorting using VBA
To implement the solution, the Desenvolvedor
must be enabled. If you have not done it yet, here's how to do it: Show Developer tab .
Taking the following example as an example:
Performthefollowingstepstoautomatesortingbythedatecolumn:
1)Createtablesortmacro
1.1)ClicktheDesenvolvedor
1.2)ClicktheVisualBasicbutton(orpressAlt
+F11
)toopentheVBAwindow
1.3)IntheInserir
menu,clicktheoptionMódulo
toaddModulo1
toVBAProject
1.4)IntheModulo1
codewindow,addthefollowing:
PublicSubOrdernarPlanilha1()ConstINI_COL_ORDENAR="Plan1!B1"
Range(INI_COL_ORDENAR).Sort Key1:=Range(INI_COL_ORDENAR), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub
2)Callsortingmacrowhenopeningspreadsheetfile
2.1)InthecodewindowoftheEstaPasta_de_trabalho
module,addthefollowingprocedure:
PrivateSubWorkbook_Open()OrdernarPlanilha1EndSub
- Thisprocedureperformssortingautomaticallywhenthefileisopened
3)Addabuttontocallthesortmacro
3.1)GobacktotheExcelwindowandclickontheDesenvolvedor
3.2)ClickontheInserirControles
buttonandselectthecontrolBotão
3.3)Drawthebuttonontheworksheet,selecttheOrdernarPlanilha1
macro,andclickOK
3.4)Changethebuttonlabelto"Sort"
4) Call the sort macro when the worksheet changes
In particular, I do not like this procedure because it changes the order of the rows while you edit the table and this can cause some confusion. But if you want to implement, just do the following:
4.1) In the VBA window, add the following code to the spreadsheet module (in case of our example it will be the module Plan1
)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OrdernarPlanilha1
End Sub
Ready,justuse!
Morethanonetableinthesameworksheet...
Ifthespreadsheethasmorethanonetabletosort,thesortroutinecanbechangedaccordingtothefollowingsuggestionstobeperformedforeachofthetables:
PublicSubOrdernarPlanilha1()OrdernarTabela"Plan1!B1"
OrdernarTabela "Plan1!B10"
End Sub
Private Sub OrdernarTabela(ini_col_ordenar As String)
Range(ini_col_ordenar).Sort Key1:=Range(ini_col_ordenar), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub