automatically sort alphabetically in vba

Question

I want to sort info on my sheet according to their last name (which is column 1). there's a form control button on the sheet that filters the info in accordance to the name of the button. everytime each of these form control buttons are clicked, i want to sort the info alphabetically. so i've used the record on macro to see what excel does to get familiar with it but i'm quite stuck... the problem is the r and countA it gives me an error of type mismatch

in total i have 17 columns (A to Q - but i don't want to really set a range to the columns, just in case i add more columns later) that contains information related to the last name which is column 1 and starts from row 3

Sub btnPrinceRupert()
Dim ws As Worksheet
Dim r As Range
Set r = ws.Cells(2, 1)
Set ws = Worksheets("Master")

Call filterMyTable(xPrinceRupert)
Call changeTitle("PrinceRupert")

    r.Select
    ws.Sort.SortFields.Clear
    ws.Sort.SortFields.Add Key:=r, _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ws.sort

    .SetRange (WorksheetFunction.CountA(Range("A:A"))) 
'essentially, i want the range to be up to the last entry of the table
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Taotao Taotao · Accepted Answer · 2014-04-15T02:28:08

For the first issue, you need to set the ws before set the r value; and the Second issue, the result of WorksheetFunction.CountA(Range("A:A")) is a number, not Range. so the code should write like this:

Sub btnPrinceRupert()
Dim ws As Worksheet
Dim r As Range
Set ws = Worksheets("Master")
Set r = ws.Cells(2, 1)
Call filterMyTable(xPrinceRupert)
Call changeTitle("PrinceRupert")

r.Select
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=r, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ws.sort
.SetRange (Range("1:" & WorksheetFunction.CountA(Range("A:A"))) 
'essentially, i want the range to be up to the last entry of the table
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

automatically sort alphabetically in vba

1 Answers