# Thread: Sort column by VBA or formula Thanks:  2 Post #5281487 (1)Post #5279617 (1) Likes:  1 Post #5279617 (1)

1. ## Sort column by VBA or formula

Hi All

I need to sort column A first by the lowest number. Column A is always a number.Then by Column B and is always a word with many duplicates.

Example:
Sheet 1

_____A______B
1____21_____Red
2____22_____Red
3____23_____Red
4____103____Green
5____104____Green
6____105____Green
7____67_____Blue
8____13_____Blue
9____74_____Blue
10___32_____Green
11___14_____Blue
12___82_____Red
13___1______Red
14___2______Red
15___3______Red
16___55_____Blue
17___56_____Blue
18___57_____Blue

Result is just the number in column A in sheet 2:
_____A
1_____1 (Red)
2_____2 (Red)
3_____3 (Red)
4____21 (Red)
5____22 (Red)
6____23 (Red)
7____82 (Red)
8____13 (Blue)
9____14 (Blue)
10___55 (Blue)
11___56 (Blue)
12___57 (Blue)
13___67 (Blue)
14___74 (Blue)
15___32 (Green)
16__103 (Green)
17__104 (Green)
18__105 (Green)

If this is best done with a macro, I would like it to run when the sheet is calculated.
I know this can be done using Sort, but I need it to be automated.

Thank you!

Russ

2. ## Re: Sort column by VBA or formula

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.
Code:
```Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = True
Sheets("Sheet1").Sort.SortFields.Clear
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets("Sheet1").Sort
.SetRange Range("A1:B" & LastRow)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.ScreenUpdating = True
End Sub```

3. ## Re: Sort column by VBA or formula

Hi Mumps
I see that this sorts column A and B in sheet 1 perfectly. My goal though is to leave sheet 1 intact and have column A in sheet 2 just show the sorted numbers from sheet 1.

ADJUSTMENT: I need to adjust Sheet1. Column A is still the number but starts at A3. Column K is the word and starts at K3.

Thanks!

4. ## Re: Sort column by VBA or formula

You said that you wanted the macro "to run when the sheet is calculated". Is it Sheet1 or Sheet2 that is calculated? Since the data is in columns A and K in Sheet1, do you want the end result in columns A and K of Sheet2 or in columns A and B of Sheet2?

5. ## Re: Sort column by VBA or formula

I would like the macro in sheet 2 to run when it is calculated.
I need the end result to be in Sheet 2 column A only. I just need the number to appear in column A (Start in A3). I don't need the name to appear in sheet 2. Column K in sheet 1 is just needed for sorting purposes.

Thank you!

6. ## Re: Sort column by VBA or formula

Try this macro in the code module for Sheet2:
Code:
```Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim LastRow As Long, srcWS As Worksheet
Set srcWS = Sheets("Sheet1")
Columns("B:B").Insert Shift:=xlToRight
Columns("A").ClearContents
With srcWS
.Range("A3", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Copy Cells(1, 1)
.Range("K3", srcWS.Range("K" & srcWS.Rows.Count).End(xlUp)).Copy Cells(1, 2)
End With
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = True
ActiveSheet.Sort.SortFields.Clear
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:B" & LastRow)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B").Delete
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub```

7. ## Re: Sort column by VBA or formula

I will try this later tonight.
Thank you very much for your help!

8. ## Re: Sort column by VBA or formula

This is really close to what I need. I just need the results in Sheet 2 to start in A3. I tried to tweak your code but didn't get the proper results.
Also, the macro doesn't run when I hit calculate. I can run it when I hit play from the developer tab.

9. ## Re: Sort column by VBA or formula

This will make the results in Sheet 2 start in A3. The macro is a Worksheet_Calculate event and is triggered when any formula in Sheet2 is calculated by changing a value referenced in the formula. For example, if you have this formula in Sheet2: =sum(A1:B1) in any cell, if you change either the value in A1 or B1 the macro will be triggered because the formula is then calculated. If you want to trigger the macro manually by clicking a button, then we have to change our approach.
Code:
```Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim LastRow As Long, srcWS As Worksheet
Set srcWS = Sheets("Sheet1")
Columns("B:B").Insert Shift:=xlToRight
Columns("A").ClearContents
With srcWS
.Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Copy Cells(3, 1)
.Range("K1", .Range("K" & .Rows.Count).End(xlUp)).Copy Cells(3, 2)
End With
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = True
ActiveSheet.Sort.SortFields.Clear
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A3:B" & LastRow)
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("B").Delete
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub```

10. ## Re: Sort column by VBA or formula

Almost there! Hope I'm not asking too much here.
I believe the macro is sorting column B alphabetically from the tests that I have tried.
I need column A to sort by the lowest number first. Then sort column B with the matching names.
Example:
11___Apple
12___Apple
1___Banana
2____Banana
7____Orange
8____Orange
31___Banana
32___Orange
33___Apple
The desired result would be:
1
2
31
7
8
32
11
12
33
So, if I were to swop Apple with Banana, the result above would be the same.
Thanks for all your help so far!