VBA - Sort by ascending and descending order
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click()
Dim iDdSortVal As Integer
Dim CurrSheet As String
Dim lFirstSectionRow As Long
Dim lLastSectionRow As Long
Dim strCol As String
Dim lRptHeaderRow As Long
Dim iRptMeasColumn As Integer
CurrSheet = ActiveSheet.Name
Application.Cursor = xlWait
Application.ScreenUpdating = False
'UnprotectSheet True
iDdSortVal = ActiveSheet.DropDowns("ddSort").ListIndex
'populate ddSort based on ddMeas contents
'Debug.Print ActiveSheet.DropDowns("ddMeas").List(ActiveSheet.D ropDowns("ddMeas").Value)
Sheets(strMeta).Visible = True
Sheets(strMeta).Select
Select Case iDdSortVal
Case 1
strCol = "B"
Case 2
strCol = "C"
Case 3
strCol = "D"
Case 4
strCol = "E"
Case 5
strCol = "F"
Case 6
strCol = "G"
Case 7
strCol = "H"
Case 8
strCol = "I"
Case 9
strCol = "J"
Case 10
strCol = "K"
Case 11
strCol = "L"
End Select
'Brewer ranking section
Sheets(strMeta).Select
lFirstSectionRow = 4
lLastSectionRow = 1357
Range(Cells(lFirstSectionRow, 1), Cells(lLastSectionRow, 12)).Select
Selection.Sort Key1:=Range(strCol & lFirstSectionRow), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'reset others and highlight specific measure column
Sheets(strReport).Select
lRptHeaderRow = 14
iRptMeasColumn = iDdSortVal + 2
Range(Cells(lRptHeaderRow, 3), Cells(lRptHeaderRow, 13)).Interior.ColorIndex = 15 'Grey
Cells(lRptHeaderRow, iRptMeasColumn).Interior.ColorIndex = 6 'Yellow
'reset others and highlight specific measure column
Sheets(strReport).Select
lRptHeaderRow = 143
iRptMeasColumn = iDdSortVal + 2
If iDdSortVal > 5 Then iRptMeasColumn = 3
Range(Cells(lRptHeaderRow, 3), Cells(lRptHeaderRow, 7)).Interior.ColorIndex = 15 'Grey
Cells(lRptHeaderRow, iRptMeasColumn).Interior.ColorIndex = 6 'Yellow
Sheets(strMeta).Visible = False
Sheets(CurrSheet).Select
'ProtectSheet True
Application.ScreenUpdating = True
Application.StatusBar = "Ready"
Application.Cursor = xlDefault
End Sub
Thanks
|
