Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 25th, 2012, 11:12 PM
STT STT is offline
Registered User
Join Date: Mar 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

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
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
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
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

'ProtectSheet True
Application.ScreenUpdating = True
Application.StatusBar = "Ready"
Application.Cursor = xlDefault

End Sub

Old April 9th, 2012, 10:24 PM
Friend of Wrox
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts


Are you trying to use the Excel's sort feature for all columns (both ascending and descending)

C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort Descending? BobSSC XSLT 2 February 25th, 2010 03:53 PM
how to sort an array ascending, help beccali Visual C++ 1 March 26th, 2007 05:19 PM
VBA Column Sort Order and numbers Jack1000 BOOK: Access 2003 VBA Programmer's Reference 1 October 5th, 2006 10:53 PM
Sort ascending and descending bertcox XSLT 1 May 17th, 2004 05:36 AM
Descending order Query problem dssachdeva SQL Language 1 September 17th, 2003 11:44 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.