PDA

View Full Version : Creating a Macro to sort data



Antonlnd
06-11-2014, 09:20 AM
Hi, what is the best way to code a macro to sort data by multiple criteria? for example if I have a spreadsheet with a ton of cities and their economic and financial data, and i want to be able to sort by 8 different range inputs by the user... i understand a simple if X < max_user input and X > min user input would do it for one of the inputs but how do I get it to then go on to the second , third, fourth ...etc criteria and only choose if it passes all of them. Also what if I want to allow the used to chose not to incorporate a sorting criteria into the data so that it bypasses testing for one of them. I have been messing around with it and have had some trouble....

ranman256
06-11-2014, 11:46 AM
If you're just sorting, just open a query.

snb
06-11-2014, 01:33 PM
Sub M_snb()
sheets("sheet1").Cells(1).CurrentRegion.Sort [A1], , [B1], , , [C1], , xlYes
End Sub


or:


Sub M_snb()
With ActiveSheet.Sort.SortFields
.Clear
.Add Range("G2:G11"), xlSortOnValues, xlAscending, xlSortNormal
.Add Range("F2:F11"), xlSortOnValues, xlAscending, xlSortNormal
.Add Range("A2:A11"), xlSortOnValues, xlAscending, xlSortNormal
.Add Range("B2:B11"), xlSortOnValues, xlAscending, xlSortNormal
.Add Range("C2:C11"), xlSortOnValues, xlAscending, xlSortNormal
.Add Range("D2:D11"), xlSortOnValues, xlAscending, xlSortNormal
.Add Range("E2:E11"), xlSortOnValues, xlAscending, xlSortNormal

With .Parent
.SetRange Range("A1:G11")
.Header = xlYes
.Apply
End With
End With
End Sub