VBA To Sort Data Based On Column Values

[excelnube]

Hi guys,

I have a simple problem, or at least i believe its simple.

I have a spreadsheet that helps me to tracks orders. I want to be able to add a bit code to my current VBA script that sorts the data based on the values in a particular column (smallest to largest).

Current table looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Order Number
[/TD]
[TD]Date
[/TD]
[TD]Quantity
[/TD]
[TD]Colour
[/TD]
[TD]Priority
[/TD]
[/TR]
[TR]
[TD]548562
[/TD]
[TD]12-09-2012
[/TD]
[TD]56
[/TD]
[TD]VBA formula
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]325415
[/TD]
[TD]01-04-2012
[/TD]
[TD]102
[/TD]
[TD]VBA formula
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]012103
[/TD]
[TD]06-10-2012
[/TD]
[TD]12
[/TD]
[TD]VBA formula
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]025486
[/TD]
[TD]26-08-2012
[/TD]
[TD]2
[/TD]
[TD]VBA formula
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]520148
[/TD]
[TD]01-01-2012
[/TD]
[TD]54
[/TD]
[TD]VBA formula
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

I want to add be able to sort the data, smallest to largest based on Column E (tittle Priority)

Any help would be appreciated.

Thanks

 

[hiker95]

excelnube,


Sample raw data (the Yellow cells contain a formula, but you did not give us the formula):

Excel Workbook
	A	B	C	D	E
1	Order Number	Date	Quantity	Colour	Priority
2	548562	12/9/2012	56	2	2
3	325415	1/4/2012	102	1	1
4	12103	6/10/2012	12	2	2
5	25486	26-08-2012	2	3	3
6	520148	1/1/2012	54	4	4
7
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	Order Number	Date	Quantity	Colour	Priority
2	325415	1/4/2012	102	1	1
3	548562	12/9/2012	56	2	2
4	12103	6/10/2012	12	2	2
5	25486	26-08-2012	2	3	3
6	520148	1/1/2012	54	4	4
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SortMyData()
' hiker95, 10/08/2012
' http://www.mrexcel.com/forum/excel-questions/663272-visual-basic-applications-sort-data-based-column-values.html
Dim lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2:E" & lr).Sort key1:=Range("E2"), order1:=1
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the SortMyData macro.


If the screenshot of after the macro is not correct, then please supply another screenshot of the results you are looking for.