• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

Filtering

This code is working great but I need to add to it   - I need three columns sorted

The First sort would be the code below

The Second sort would be by column D2 ascending which has numbers and text.  I need the number values sorted first in this column the text will say "None" or could be null.

The Third sort would be by Column O2  ascending which is text.


Private Sub sortmainlist()
Dim rng As Range
Dim listnum As Long
 
On Error Resume Next
Application.AddCustomList ListArray:=Array("Top", "Middle", "Bottom")
listnum = Application.GetCustomListNum(Array("Top", "Middle", "Bottom"))

Thank you in advance.
 
With Sheets("Sheet1")
    .Activate
    Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 5))
End With
 
'Note: listnum+1
rng.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=listnum + 1, _
    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal
     

Application.DeleteCustomList listnum
     
End Sub
0
Medicine1
Asked:
Medicine1
  • 4
  • 3
  • 2
1 Solution
 
bromy2004Commented:
Could you provide data to test?
0
 
Medicine1Author Commented:
I have included data on sheet2 and code in attached.  Let me know if you need anything else.  

1st sort will be column F
2nd by column B
3rd by column C

Thank you!!
Book3.xlsx
0
 
bromy2004Commented:
Did you mean something like the attached?
Sub Macro1()
  With ActiveWorkbook.Worksheets("Sheet2").Sort
    .SortFields.Clear
    .SetRange Range("A1:F13")
    .Header = xlYes
    .SortFields.Add Key:=Range("F2:F5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("B2:B5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("C2:C5000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End Sub

Open in new window

Book3.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
patrickabCommented:
Medicine1,

In this question you started out by saying that you wanted it sorted by:

1st column F
2nd column D
3rd column O

now your most recent requirement is:

1st sort will be column F
2nd by column B
3rd by column C

I am going to take it that in the original file you are now wanting it as your most recent requirement as there is no data in column O.

Patrick
0
 
patrickabCommented:
Medicine1,

The modified sort routine is below and in the attached file. I have followed your latest instruction - namely:

1st sort will be column F
2nd by column B
3rd by column C

In the attached file I have changed all the businesses and all the Employee Names & numbers.

Hope the sorting routine is what you require. I believe that when you have real employee names it will sort it correctly. At the moment the numbers make the sort routine go in text number order not pure number order. When you only have only proper names in column B it will sort it correctly.

Patrick
Private Sub sortmainlist()
Dim rng As Range
Dim listnum As Long

On Error Resume Next
Application.AddCustomList ListArray:=Array("Top", "Middle", "Bottom")
listnum = Application.GetCustomListNum(Array("Top", "Middle", "Bottom"))

With Sheets("Sheet1")
    .Activate
    Set rng = Range(.Cells(2, "A"), .Cells(.Rows.Count, 1).End(xlUp).Offset(0, 5))
End With

'Comment ID:26077586
'1st sort will be column F
'2nd by column B
'3rd by column C

'Note: listnum+1
rng.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("B2") _
        , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
        xlNo, OrderCustom:=listnum + 1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
    
'NOTE: listnum Not listnum+1
Application.DeleteCustomList listnum
    
End Sub

Open in new window

reorg-08.xls
0
 
Medicine1Author Commented:
I am reattaching the file for the code above.  The Names are not sorting.  I added some names to see.

I told you wrong
The columnas are

F
B
D
sort order
but replacd C with D and did not work...

reorg-09.xls
0
 
Medicine1Author Commented:
Never mind  Patrick... looks like yours is working. I have to figure why not working in my sheet....

Thank you....
0
 
Medicine1Author Commented:
Works Great
0
 
patrickabCommented:
Medicine1 - Thanks for the grade - Patrick
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now