Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
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
Novice
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".

HELP!

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
  #2  
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
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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
Reply

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

All times are GMT -7. The time now is 09:17 AM.


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