Edit/Expansion of Sort macro needed

JONPM

New Member
Joined
Apr 17, 2013
Messages
24
Hi all,

I've provided below a simplified version of the worksheets I'm trying to sort and the Macro I've recorded to sort the worksheet, however, I need to edit/update the macro for use on all the worksheets in the workbooks I'm sorting and specifically to REFINE the sort criteria for Column D and Column E.

The sort is run simultaneously and runs hierarchically A,B,D,E,F,G.

In column D I'd like the macro to sort numerically as it is doing but to IGNORE the prefix c. before the year when it occurs in the column.

In column E I'd like the macro to sort alphabetically as it is doing but to IGNORE any inverted commas: ' ' around the text as these are messing up the alphabetical sort for this column.


ABCDEFG
PatrickAnn(possibly)1910Apple on TableART10020WMRII_BBCO_1771.jpg
JonesSimonWoman in BlueART10013WMRII_BBCO_1022.jpg
ScottPeter(after) 1920Forest GladeART10024WMRII_BBCO_1220.jpg
ScottPeter(attributed)c.1919'Still Life with Bottle'ART10030WMRII_BBCO_1300.jpg
ScottPeter(circle of)1930Snow-scene with LarchesART10015WMRII_BBCO_13.jpg
ScottPeter(school of)1937'Shrimp Seller'ART10013WMRII_BBCO_1322.jpg
ScottPeter1910ART10022WMRII_BBCO_1344.jpg
PurvisJackUntitledART10085WMRII_BBCO_1399.jpg
AshleySheilaMediterranean ViewART10076WMRII_BBCO_1000.jpg
AshleySheila'Landscape'ART10092
WMRII_BBCO_1348.jpg

<tbody>
</tbody>

Below is the recorded Macro which I've called MANUAL_SORT2 this was recorded on a worksheet called BBCO with 103 rows in the column. I like the macro to be usable on all worksheets in the workbook also to add the worksheets all have varying numbers of rows!


Sub MANUAL_SORT2()
'
' MANUAL_SORT2 Macro
'


'
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("A2:A103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("B2:B103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("C2:C103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("D2:D103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("E2:E103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("F2:F103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("BBCO").Sort.SortFields.Add Key:=Range("G2:G103"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("BBCO").Sort
.SetRange Range("A1:AB103")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub



Thanks in advance if any of you can help! :confused:
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

JONPM

New Member
Joined
Apr 17, 2013
Messages
24
I've managed to edit the macro to this so that it works on all worksheets with columns of varying length ...... just now how to refine the search criteria...

Sub MANUAL_SORT2()
'
' MANUAL_SORT2 Macro
'


'
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("A2:A" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("D2:D" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("E2:E" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("F2:F" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("G2:G" & LR), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:G" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,292
Messages
5,600,765
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top