VBA sort array

[Panoos64]

Hi all, I would like to avoid use any excel native functions and i would like to use vba code so that to sort the range of rows "A9" : "M" and rows down, sorting them by column "B" A:Z (alphabetical)
Thank you all in advance

 

[Fluff]

The simplest & easiest solution is to sort the data on the sheet using the inbuilt Sort.
If you must do it with VBA have a look here http://www.cpearson.com/excel/SortingArrays.aspx

 

[Panoos64]

Hi Fluff, the above is a bit complicate for me and usually i use the below code, which i assign it into macro command, using "Record" function. Just i need an advice how to adapt it to any name of sheet. Based on below is for a specific Sheet named "Statement Debtors Ageing"

Thank you in advance


Sub SortbyName()
ActiveWorkbook.Worksheets("Statement Debtors Ageing").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Statement Debtors Ageing").Sort.SortFields.Add Key _
:=Range("B5:B10001"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Statement Debtors Ageing").Sort
.SetRange Range("A4:I10001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Fluff]

If you want it to work on the active sheet, then change

ActiveWorkbook.Worksheets("Statement Debtors Ageing")

to

ActiveSheet

 

[Panoos64]

I express my thanks once again Fluff. long time ago i was assign a macro for each spreadsheet when i wanted to sort the data. Many thanks. Hv a great weekend

 

[Fluff]

Glad to help & thanks for the feedback