Sorting to Material ending with "S" and rest in ascending

[PRADEEPB270]

Please refer my attach file.

I want to sort my column 'B' i.e. "Material" ending with text "S" and rest in ascending orders and all the datas according to the order.I have put the VBA codes for that purpose but unable to find the desired result.Desired correction in my vba codes will be helpful to me.

[Rudi]

Please refer my attach file.

I want to sort my column 'B' i.e. "Material" ending with text "S" and rest in ascending orders and all the datas according to the order.I have put the VBA codes for that purpose but unable to find the desired result.Desired correction in my vba codes will be helpful to me.

Hi Pradeep,

Try this code...
Note: Please ensure the list starts in B5 and also delete the word "Current" in B4. Headings cannot be over two rows. If it is left like this is will cause the macro to not run propely.

Code: Select all

Sub SortList()
Dim rData As Range
Set rData = Range("B5").CurrentRegion
    Application.ScreenUpdating = False
    rData.Columns(2).EntireColumn.Insert
    rData.Columns(2).Cells(1) = "Calc"
    rData.Columns(2).Offset(1).FormulaR1C1 = "=RIGHT(RC[-1],1)"
    rData.Sort Key1:=rData.Cells(1).Offset(0, 1), Key2:=rData.Cells(1), Header:=xlYes
    rData.Columns(2).EntireColumn.Delete
    Application.ScreenUpdating = True
End Sub

[PRADEEPB270]

Macro is working perfect.Thanks Rudi for help.