Sort asscending

[Damo10]

Hi,

I have several sheets where I would like to sort the data in rows 11 to the last used row in assending order, the data starts in cell B11 and the last column is DL but I would like the sort based on the name in column C.

Could someone show me how this would be done with VBA?

Regards

 

[Trevor G]

I recorded a macro to do a custom sort based on a selected set of cells and it came back OK, see if you can adapt this to a range that you need.

Sub mcrSort()
'
' mcrSort Macro
'
'
Range("A11:A20").Select
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet4").Sort.SortFields.Add Key:=Range("A11:A20") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet4").Sort
.SetRange Range("A11:A20")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[shg]

Or similarly,

Sub SortIt()
    Dim wks       As Worksheet
 
    Set wks = Worksheets("Sheet1")  ' change as required
 
    With wks.Sort
        .SortFields.Clear
        .SetRange Intersect(wks.UsedRange, _
                            wks.Range("A11", wks.Cells(wks.Rows.Count, wks.Columns.Count)))
        .SortFields.Add Key:=wks.Range("C11"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
        .Header = xlYes         ' or xlNo
        .MatchCase = False      ' or True
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

 

[Damo10]

Hi SHG

I have just tried your code and am getting a runtime error 438 on line

.SetRange Intersect(.UsedRange, .Range("A11", Cells(.Rows.Count, .Columns.Count)))

Regards

 

[shg]

Grab the code again from my post.