Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Thread Tools Display Modes
Old 04-12-2016, 08:09 AM
tandchas tandchas is offline VBA Sort Custom Order Windows 7 64bit VBA Sort Custom Order Office 2010 64bit
VBA Sort Custom Order
Join Date: May 2014
Posts: 7
tandchas is on a distinguished road
Default VBA Sort Custom Order

Trying to sort ALL data automatically...sort if something is entered in Column B or C...headers in row 1...first sort based upon Column C (dates) in descending order. That all seems to be working.

What seems to not be working is the fact that I want it also to sort based upon values in Column B; after it sorts based upon Column C. The values in Column B is either 1, 2, or 3. But, I want the sort order to be "2, 1, 3".


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("B:C")) Is Nothing Then

Range("C1").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
Key2:=Range("B2"), Order2:=xlDescending, Header:=xlYes, OrderCustom:="2, 1, 3", MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub
Reply With Quote
Old 04-15-2016, 05:38 PM
BobBridges's Avatar
BobBridges BobBridges is offline VBA Sort Custom Order Windows 7 64bit VBA Sort Custom Order Office 2010 32bit
Join Date: May 2013
Location: USA
Posts: 678
BobBridges will become famous soon enoughBobBridges will become famous soon enough

I don't recommend that you use my workaround. Much better that you keep trying until you get this OrderCustom thingy to work, or that someone here explain it to you. But I've never needed to use it; consequently I can't be the one to help. So if no one here answers, and you can't figure it out yourself, it is possible if you're desperate to do this:
  1. Create a helping column to the right.
  2. Place in the helping column a set of values that correspond to the contents of col B, only in the correct order; that is, where B has a 2 put 1 in the helping column, where B has 1 put 2, and where B has 3 put 3.
  3. Sort by C and then by the helping column.
  4. Delete the helping column.
That'll work. But if you can figure out Excel's customized sorting you'll be 'way ahead of the game.
Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Sort Custom Order Custom sort - two header_lines? Officer_Bierschnitt Excel 1 11-19-2015 07:48 AM
VBA Sort Custom Order Selecting values from different rows in a table and sort them in order in a single row FromF Excel 2 09-09-2014 02:30 AM
How to sort word text in alphabetic order . crossing Word 8 01-20-2012 09:51 AM
Outlook 2007 Contacts Sort Order bianson Outlook 2 01-27-2011 10:32 AM
list of documents come up in random order - any ideas how to sort this? tallscot49 Word 0 07-18-2010 12:40 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:47 PM.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd. is not affiliated with Microsoft