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
Answer
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
Attribution
Source : Link , Question Author : Anusha , Answer Author : Andy