troubleshooting Question

sort on column

Avatar of AaronBanker
AaronBanker asked on
Microsoft ExcelVBA
6 Comments1 Solution180 ViewsLast Modified:
I had this question after viewing sort on column.

I am sorting data by a category, then I need to sort the id in sequential order to select an item from the list box.  I have completed the sort by column (Sub SortDataByAcctCode()),  but keep getting an error message when i sort by the ID (Sub SortID())  I always get an error message starting at ".AutoFilter.sort.SortFields.Clear" in the (Sub SortID()

Whats happening

Sub SortDataByAcctCode()
'
' SortDataByAcctCode Macro

    Application.ScreenUpdating = False
    Sheets("Expenses").Visible = True
    
    With Sheets("Expenses")
        .Range("A1:Z1").AutoFilter
     
        ActiveWindow.SmallScroll ToRight:=3
     
        .AutoFilter.sort.SortFields.Clear
     
        .AutoFilter.sort.SortFields.Add Key:= _
            .Range("H1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
     
        With .AutoFilter.sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
     
        End With
     
        .Range("A1:Z1").AutoFilter
     
    End With
    
    Sheets("Expenses").Visible = False
    Application.ScreenUpdating = True

End Sub


Sub SortID()
'
' SortID Macro
'
    Application.ScreenUpdating = False
    Sheets("Expenses").Visible = True

    With Sheets("Expenses")
        
        .Columns("A:A").Select
        
        .AutoFilter.sort.SortFields.Clear

        .AutoFilter.sort.SortFields.Add Key:= _
            .Range("A2:A2254"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal

        With .AutoFilter.sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    End With
    
    Sheets("Expenses").Visible = False
    Application.ScreenUpdating = True
    
End Sub
    
ASKER CERTIFIED SOLUTION
gowflow
Partner

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros