How to I fix this VBA code for special sort so that I do not get object errors?

user_18461110 0
Viewed 62

I am using a special sort to sort multiple columns in a spreadsheet, in this case- 3. However, the SetRange and the code below it seems to be gettings an error of "Object doesn't support this property or method."

if I take away the With statement I have a different error of "Invalid or unqualified sequence."

the line that is highlighted is .SetRange Range("A1:I35") and the error is 438 Object doesnt support this property or method

How do I proceed in fixing this error?

Sub Sort()
With ActiveSheet
' Sort Macro

Cells.Select

    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add2 Key:=Range("A2:A35" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   ActiveSheet.Sort.SortFields.Add2 Key:=Range("B2:B35" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   ActiveSheet.Sort.SortFields.Add2 Key:=Range("H2:H35" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:I35")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
End Sub
vba excel
2 Answers

Sort a Range

Sub SortData()

    'Dim wb As Workbook: Set wb = ThisWorkbook
    'Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    With ws.Range("A1").CurrentRegion
        .Sort _
            Key1:=.Columns(1), Order1:=xlAscending, _
            Key2:=.Columns(2), Order2:=xlAscending, _
            Key3:=.Columns(8), Order3:=xlAscending, _
            Header:=xlYes
    End With

End Sub
user_98140690

You aren't quite using the With statement correctly. Try it like this:

Sub Sort()
    With ActiveSheet.Sort
    
        Cells.Select
    
        .SortFields.Clear
        .SortFields.Add2 key:=Range("A2:A35" _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 key:=Range("B2:B35" _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 key:=Range("H2:H35" _
            ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:I35")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
user_1091220
Related