Home > front end >  Error 1004 keeps popping out when trying to execute the sort VBA command
Error 1004 keeps popping out when trying to execute the sort VBA command

Time:05-13

Part 1:

Please help to advise where did it went wrong. Would like to sort for column E via descending order with header

Sub SortRows()

Dim destSht As Worksheet
Set destSht = ThisWorkbook.Worksheets("Account Level")

destSht.Sort.SortFields.Clear
Range("E2", destSht.Cells(destSht.Rows.Count, "E").End(xlUp)).Sort Key1:=Range("E2"), 
Header:=xlYes, _
Order1:=xlDescending

End Sub

Part 2: How should I input the vba code in a way which it could dynamically filter column C based on descending values? enter image description here

CodePudding user response:

Maybe

destSht.Range("E2", destSht.Cells(destSht.Rows.Count, "E").End(xlUp)).Sort Key1:=destSht.Range("E2"), 
Header:=xlYes, _
Order1:=xlDescending

Based on latest comments 12-May-2022

Sub sortRows()
    With Worksheets("Account Level")
        .sort.SortFields.Clear
        .Cells(1, 1).CurrentRegion.sort key1:=.Cells(2, 4), order1:=xlDescending, Header:=xlYes
        .Cells(1, 1).CurrentRegion.AutoFilter
    End With
End Sub
  • Related