How to Sort Numbers within Sorted Column VBA

Question

So I have four columns of data, but only two (Column C and D) that I wish to sort. One column is a list of Work Center Names (Column C) and the other is Operation Numbers (Column D). What I want to know is how to sort the Work Center Names in Alphabetical Order and then within each Work Center Name, Sort the Operation Numbers in Ascending Order.

So the Data would end up looking like: (assuming x is a random column value)

x   x   LDHF   10
x   x   LDHF   20
x   x   LDHF   30
x   x   SHFT   10
x   x   SHFT   20
x   x   SHFT   30

Currently, this is my code. It sorts one column but then when it sorts the second, it overwrites the first sort and scrambles the other column.

   ActiveWorkbook.Worksheets("Percent").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Percent").Sort.SortFields.Add Key:=Range( _
      "D1:D" & DailyLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      xlSortNormal
   With ActiveWorkbook.Worksheets("Percent").Sort
      .SetRange Range("A1:D" & DailyLastRow)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
      End With 

   ActiveWorkbook.Worksheets("Percent").Sort.SortFields.Clear
   ActiveWorkbook.Worksheets("Percent").Sort.SortFields.Add Key:=Range( _
      "C1:C" & DailyLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
      xlSortNormal
   With ActiveWorkbook.Worksheets("Percent").Sort
      .SetRange Range("A1:D" & DailyLastRow)
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
      End With

Luc Berthiaume · Accepted Answer · 2014-10-24 16:23:10Z

1

You can use range.sort.

You have option to define multiple column to sort with key1 := range.columns(3), key2 := range.columns(4)

Here is the table before

Col1    Col2    Col3    Col4

x       x       LDHF    30

x       x       SHFT    10
x       x       LDHF    20
x       x       SHFT    20
x       x       SHFT    30
x       x       LDHF    10

Here is the script

Sub RangeSort()

Dim rng As Range
Set rng = Range("a1:D7")

rng.Sort key1:="Col3", order1:=xlAscending, Key2:="Col4", Header:=xlYes

End Sub

And this is the output :

Col1    Col2    Col3    Col4
x       x       LDHF    10
x       x       LDHF    20
x       x       LDHF    30
x       x       SHFT    10
x       x       SHFT    20
x       x       SHFT    30

I guess it is what you wanted.

edited Oct 24, 2014 at 16:23

answered Oct 24, 2014 at 12:41

Luc Berthiaume

3161 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

ksmit144 Over a year ago

Could you explain a little further?

ksmit144 Over a year ago

I just tried to use that code and for some reason the numbers remain out of order, although the workcenters are correctly alphabetized. I am not sure what is wrong with the code, but this is what I used:

Dim rng As Range Set rng = Range("A1:D" & DailyLastRow) rng.Sort key1:=Range("C1:C" & DailyLastRow), order1:=xlAscending, Key2:=Range("D1:D" & DailyLastRow), Header:=xlYes

Luc Berthiaume Over a year ago

you wanna sort col 3 after you sort col 4 ? this means you will get '10 10 20 20 30 30' and then 'LDHF SHDF LDHF SHDF LDHF SHDF'

ksmit144 Over a year ago

Sorry I just corrected it. I used the code correct in my sub, I just typed it in wrong here.

Luc Berthiaume Over a year ago

Then try this set rng = Range("A1:D" & DailyLastRow) rng.Sort key1:=rng.Columns(3), order1:=xlAscending, key2:=rng.Columns(4), Header:=xlYes

|

Jur Pertin · Accepted Answer · 2014-10-24 12:50:37Z

0

use the below code. This will do your job. Let me know if it does not work.

Sub PSortData()

    Dim rngData As Range

    Set rngData = Worksheets("Percent").Range("A1:D" & DailyLastRow)

    rngData.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("D1"), Order2:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

    Set rngData = Nothing

End Sub

answered Oct 24, 2014 at 12:50

Jur Pertin

5644 silver badges9 bronze badges

2 Comments

ksmit144 Over a year ago

Thanks for the reply, but this code sorts the data pretty much the same as the code I posted above. It sorts one column or the other, not one within the other.

Jur Pertin Over a year ago

@ksmit144 what is it you looking for then? Could you re-iterate your question?

Collectives™ on Stack Overflow

How to Sort Numbers within Sorted Column VBA

2 Answers 2

8 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related