Sort from Largest to Smallest

user_10896304 0
Viewed 285

I need to sort the table from Largest to Smallest as column Q.

codes gives no errors but doesnt work as well. It firstly filters well and sorts with no success:

introws = Range("A1").End(xlDown).Row
ActiveSheet.Range("$A$1:$q$" & introws).AutoFilter Field:=17, Criteria1:=">4", Operator:=xlFilterValues
Range("$A$1:$q$" & introws).Sort Key1:=Range("q:q"), Order1:=xlDescending
Range("q:q").Sort (xlDescending)

Both lines dont work, it just stays unsorted.... How to write this?

vba
1 Answers

To sort a range value with filter, you need to apply specific method in a sequence, it involve clear sortfields > add key > sort.apply, the following is a simple sort for the range with data filter:

With Sheet1
    .AutoFilter.Sort.SortFields.Clear
    .AutoFilter.Sort.SortFields.Add2 Key:= _
    Range("B1"), SortOn:=xlSortOnValues, Order:=xlDescending
    .AutoFilter.Sort.Apply
End With

enter image description here

To apply the sort method in your case, you may try to following code modication, it should be work as expected, please perform a test for both case:

introws = Range("A1").End(xlDown).Row
ActiveSheet.Range("$A$1:$q$" & introws).AutoFilter Field:=17, Criteria1:=">4", Operator:=xlFilterValues

With ActiveSheet
    .AutoFilter.Sort.SortFields.Clear
    .AutoFilter.Sort.SortFields.Add2 Key:= _
    Range("Q1"), SortOn:=xlSortOnValues, Order:=xlDescending
    .AutoFilter.Sort.Apply
End With
user_158978610
Related