CODEWITHSUNDEEP
vbaexcelsorting
os23
os23

Reputation: 145

How to sort an Excel sheet by two different columns

Let's assume a chart with only values in the first two columns A and B like this

enter image description here

First I'd like to sort the chart alphabetically by column A, i.e. the Xs at top, then the Ys and the Zs.

The 2nd move should be a sorting of column B considering column A, i.e. keeping the block of Xs and sort their numbers in column B. The same with the blocks of Y and Z.

The 1st step works fine with the following code:

Sub sort()
Dim SortClmn As String
Dim Area As String
Area= "A1:B10"
SortClmn = "A"
ActiveSheet.Range(Area).Sort _
Key1:=Range(SortClmn & "1"), Order1:=xlDescending, _
Header:=xlGuess, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

How do I need to change this code to apply the 2nd step?

Thanks in advance

Upvotes: 0

Views: 125

Answers (3)

Radek Piekný
Radek Piekný

Reputation: 145

Sub sort()
    lastRow = ActiveWorkbook.ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    With ActiveWorkbook.ActiveSheet.sort
     .SortFields.Clear
     .SortFields.Add Key:=Range("A1:A" & lastRow), Order:=xlAscending
     .SortFields.Add Key:=Range("B1:B" & lastRow), Order:=xlAscending
     .SetRange Range("A2:B" & lastRow)
     .Header = xlNo
     .Apply
    End With
End Sub

Something like this?

Upvotes: 0

Ricards Porins
Ricards Porins

Reputation: 384

The simplest method is to sort Column B and then sort Column A, this will achieve your desired effect. Use the same method you used, but sort first column b then column a.

If I modify your code it would look as follows

Sub sort()
Dim SortClmn As String
Dim Area As String
Area = "A1:B10"
SortClmn = "B"
ActiveSheet.Range(Area).sort _
Key1:=Range(SortClmn & "1"), Order1:=xlAscending, _
Header:=xlGuess, MatchCase:=False, _
Orientation:=xlTopToBottom
Area = "A1:B10"
SortClmn = "A"
ActiveSheet.Range(Area).sort _
Key1:=Range(SortClmn & "1"), Order1:=xlAscending, _
Header:=xlGuess, MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43595

  1. Record a macro;
  2. Select the table;
  3. In the ribbon - Data > Sort;

enter image description here

  1. Select the first column that you want to sort;
  2. Press Add Level;
  3. Select the second column that you want to sort;
  4. See the generated code;;
  5. Refactor it, avoinding Select, Activate and putting the string variables;

Range.Sort Method MSDN

Upvotes: 0

Related Questions