0

I want to switch the sorting from ascending to descending whenever the variable in a column of my dataset changes. An example to demonstrate the sorting I want is as follows. I tried adding another column to add an odd or even number and then sort ascending when even and descending when odd but donot know how to make it work. How do I alternate sort these variables by macro as not possible to do this manually for large dataset?

ID  Sales
Apple   10
Apple   2
Apple   12
Orange      15
Orange      4
Guava   2
Guava   18
Guava   20
Guava   3


ID  Sales
Apple   12
Apple   10
Apple   2
Orange      4
Orange      15
Guava   20
Guava   18
Guava   3
Guava   2

I found the VBA code to insert blank row when variable changes.link Can this be modified to change sorting order?

    sub AddBlankRows()
'
dim iRow as integer, iCol as integer
dim oRng as range

set oRng=range("a1")

irow=oRng.row
icol=oRng.column

do 
'
if cells(irow+1, iCol)<>cells(irow,iCol) then
    cells(irow+1,iCol).entirerow.insert shift:=xldown
    irow=irow+2
else
    irow=irow+1
end if
'
loop while not cells (irow,iCol).text=""
'
end sub
  • Use an actual system that is meant to be used as a database. Even if it is just Access. Your example just asks for that. Anyway, you should probably look into Range.Sort and a way to register a new value in a column. After that sort for ID, group by ID and sort based on number of items in group. The idea itself actually sounds pretty horrible as it would be greatly inconsistent, especially with your example that doesn't even make any sense. – Seth Mar 14 '17 at 13:20
  • Just to point it out: Apple is odd and sorted descending, Orange is even and sorted ascending, Guava is even and descending. Most value with the exception of 15 and 3 are even, so they can't be related to it as well? This example doesn't fit whatever you described to do or vice versa. – Seth Mar 14 '17 at 13:23
  • This is just an example to show how I want to sort. Yes, I want to first sort in ascending order and for next variable in descending order. There is no relation between the numbers. They are just for demo purposes. – Anusha Mar 14 '17 at 14:55
  • Still Range.Sort and grouping would be the solution. Without any more specific information it's probably as good as it gets, if there is no build in way. – Seth Mar 14 '17 at 18:55
0

This VBA code ask for the Input number (any number even for ascending, odd for descending) test it with Mod function and sort the Data "A1:B11" with Header you can change it to your real Data (sort on column 1 (A) ascending, column 2 (B) ascending or descending according to the Input Number

Sub DataSort()  
    Dim theRange As Range 
    Dim rep As Variant 
    Set theRange = Range("A1:B11") 
    rep = InputBox("Enter a number to decide order") 
    If rep Mod 2 = 0 Then    
      With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=theRange.Columns(1).Cells, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=theRange.Columns(2).Cells, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange theRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
     End With
    ElseIf rep Mod 2 = 1 Then
        With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=theRange.Columns(1).Cells, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=theRange.Columns(2).Cells, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
         With .Sort
            .SetRange theRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
        End With
    End If
End Sub
|improve this answer|||||
  • Thanks for the reply Yass. I might be missing something basic but dont see any sorting happening when I run this code. Does the data need to selected in a particular way for this to work? Also, there is no sorting on different columns. If the variable in main column changes, then the sorting changes from ascending to descending. ie. when variable changes from apple to orange, the sorting of sales changes from descending to ascending order. – Anusha Mar 15 '17 at 5:14
  • you have to change A1:B11 to your reference (if you have other columns C, D...and column 1 should be ID in A and column 2 means Sales in B and input 1 or 2 in the Input box – yass Mar 15 '17 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.