recorded macro to VBA code

[Krishna2385]

Hi I have a MACRO recorded for a custom sort list. can I get the same in VBA coding.

am pasting my macro and need the same to be in VBA coding, I was told that there will be difference in macro recording to write up the VBA code

Sub sort()
'
' sort Macro
'

'
Selection.AutoFilter
Application.DeleteCustomList ListNum:=6
Application.AddCustomList ListArray:=Array("Tier 4", "Tier 3", "Tier 2", _
"Tier 1")
ActiveWorkbook.Worksheets("eGRC").AutoFilter.sort.SortFields.Clear
ActiveWorkbook.Worksheets("eGRC").AutoFilter.sort.SortFields.Add Key:=Range( _
"A2:A12140"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("eGRC").AutoFilter.sort.SortFields.Add Key:=Range( _
"D2:D12140"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Tier 4,Tier 3,Tier 2,Tier 1", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("eGRC").AutoFilter.sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Chihiro]

It's pretty much the same thing.
Difference being, that since recorded Macro records your action as it happens, it isn't optimized.

For an example, .Select, Selection, Activate etc are not needed when code is scripted rather than recorded.

I'd recommend you upload sample workbook that macro is intended to run on, to get help.

 

[NARAYANK991]

Hi ,

As already suggested , it would be helpful if you could upload your workbook with the data in it.

If you cannot , then try this :

Public Sub SortVBA()
          Dim CustomList1 As Variant
          CustomList1 = Array("Tier 4", "Tier 3", "Tier 2", "Tier 1")
         
          Application.DeleteCustomList ListNum:=6
          Application.AddCustomList ListArray:=CustomList1

          With ActiveWorkbook.Worksheets("eGRC").Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("A2:A12140"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=Range("D2:D12140"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=CustomList1, DataOption:=xlSortNormal
                .SetRange = Range("A1:D12140")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
          End With
End Sub

Narayan

 

[Krishna2385]

Thank you so much for the quick response guys......

there is a run time error 13 while I use the above mentioned code.
And I cannot upload this file

the error is for below mentioned line :

.SortFields.Add Key:=Range("D2:D12140"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=CustomList1, DataOption:=xlSortNormal

 

[Krishna2385]

To give a better understanding there are a list of suppliers in column A and I need that to be sorted from AtoZ.
there is a set of data in column D which should be sorted from Tire 4 to Tire 1 and I even have blanks in them.

Example :

A D
Supplier ABC Tire 1
Supplier ABC Tire 3

So when I run this code the column A should be sorted from AtoZ and the column D should be from higher number to lower followed by blank.

A D
Supplier ABC Tire 3
Supplier ABC Tire 1

 

[Chihiro]

I'm a bit confused. Why do you need custom sort then?

1. AtoZ Ascending
2. AtoZ Descending

Can't you accomplish same with above?

If you have sensitive info in the workbook, you just need to replicate the set up with desensitized info and upload it here. Code logic won't change.

 

[Krishna2385]

I'm a bit confused. Why do you need custom sort then?

1. AtoZ Ascending
2. AtoZ Descending

Can't you accomplish same with above?

If you have sensitive info in the workbook, you just need to replicate the set up with desensitized info and upload it here. Code logic won't change.

Nope ... I tried the same but with normal sorting the i wont get desired output. will replicate and upload the file.
thanks once again for being patience enough

 

[NARAYANK991]

Hi ,

Try this :

Public Sub SortVBA()
          With ActiveWorkbook.Worksheets("eGRC").Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("A2:A12140"), SortOn:=SortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=Range("D2:D12140"), SortOn:=SortOnValues, Order:=xlAscending, CustomOrder:="Tier 4,Tier 3,Tier 2,Tier 1", DataOption:=xlSortNormal
                .SetRange Range("A1:D12140")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
          End With
End Sub

Narayan

 

[Krishna2385]

Thank you soo much ...

 

[NARAYANK991]

Hi ,

Just for the record , the above VBA Sort should be as follows :

Public Sub SortVBA()
          Dim CustomList1 As Variant
          CustomList1 = Array("Tier 4", "Tier 3", "Tier 2", "Tier 1")
        
          Application.DeleteCustomList ListNum:=Application.CustomListCount
          Application.AddCustomList ListArray:=CustomList1

          With ActiveWorkbook.Worksheets("eGRC").Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("A2:A12140"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .SortFields.Add Key:=Range("D2:D12140"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=Application.CustomListCount, DataOption:=xlSortNormal
                .SetRange Range("A1:D12140")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
          End With
End Sub

The parameter CustomOrder takes the number of the custom list !

Secondly , when the custom list is created , the order is an ascending order ; in the above example , Tier 4 is less than Tier 1.

Thus , if the ascending order is Tier 1 , Tier 2 , Tier 3 and Tier 4 , then when assigning the custom list to the variable CustomList1 , the assignment should be :

CustomList1 = Array("Tier 1", "Tier 2", "Tier 3", "Tier 4")

Narayan