Sorting Challenge

Colin Legg · #1 06-21-2012, 08:51 AM

It's because those "empty" cells in column B aren't actually empty.

Using your attached workbook and code as an example, you can force the empty strings to be cleared from the cells like this:

Code:

Sub Example()
 
    With Sheet1
        'get rid of any null strings stored in the cells
        With .Range("A1:L15")
            .Value = .Value
        End With
 
 
        With .Sort
 
            'set up the sort fields
            With .SortFields
                .Clear
                .Add Key:=Sheet1.Range("A1:A15"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
 
                .Add Key:=Sheet1.Range("B1:B15"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply
            .SetRange Sheet1.Range("A1:L15")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Sorting by certain criteria	randenius	Excel	2	06-11-2012 02:18 AM
Word Challenge	jpotter2	Word	3	03-22-2011 02:07 PM
Challenge!! Need help though. (Conditional formatting)	knuckles70	Excel	2	02-05-2010 12:24 PM
CHALLENGE! Issue sorting data containing relative references on a separate tab	lax828	Excel	3	01-07-2010 11:28 AM
Challenge: Get Custom Form to Show up in Reader Pane in 2007	JohnGG	Outlook	0	08-21-2009 05:44 AM