Sort columns using VBA from newest to oldest

Question

I want a button to complete a range of steps within an Excel workbook to get it in a suitable place for upload. I need to sort data, newest to oldest, within the Date column after completing a filter, delete, remove filter steps.

Sub All_Steps ()

'Filters Closed Incomplete and Closed Skipped within the state column
Worksheets("Update Template").Range("A1:E15000").AutoFilter Field:=5, Criteria1:="Closed Incomplete", Operator:=xlOr, Criteria2:="Closed Skipped"

'Deletes Closed Incomplete and Closed Skipped filtered rows
Application.DisplayAlerts = False
Worksheets("Update Template").Range("A2:E15000").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

'Removes filter
Worksheets("Update Template").ShowAllData
'Updates DD-MM-YYYY HH:MM:SS date format to permissible DD/MM/YYY
Worksheets("Update Template").Range("D2:D30000").NumberFormat = "DD/MM/YYY"

End Sub

The code I've tried for sorting:

Worksheets("Update Template").Range("A1:E15000", Range("A1:E15000").End(xlDown)).Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlYes

FYI there's a Typo in your attempted code: Key1:=Range("D1) should be Key1:=Range("D1"). — BruceWayne
– BruceWayne, Commented Apr 14, 2023 at 16:12
@BruceWayne Thanks! It's correct in my actual attempted code, just a typo when typing this up! — Ryan
– Ryan, Commented Apr 14, 2023 at 16:52

Tim Williams · Accepted Answer · 2023-04-14 18:25:27Z

Try this:

Sub All_Steps()
    Dim ws As Worksheet, rng As Range 'use some variables!
    
    Set ws = Worksheets("Update Template")
    
    Set rng = ws.Range("A1:E" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
    
    'Filters Closed Incomplete and Closed Skipped within the state column
    rng.AutoFilter Field:=5, Criteria1:="Closed Incomplete", _
                      Operator:=xlOr, Criteria2:="Closed Skipped"

    'Deletes Closed Incomplete and Closed Skipped filtered rows
    Application.DisplayAlerts = False
    rng.Offset(1).Resize(rng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete 'preserve headers
    Application.DisplayAlerts = True

    'Removes filter
    ws.ShowAllData
    'Updates DD-MM-YYYY HH:MM:SS date format to permissible DD/MM/YYY
    rng.Offset(1).Columns(4).NumberFormat = "DD/MM/YYY"
    rng.Sort key1:=rng.Columns(4), order1:=xlDescending

End Sub

Collectives™ on Stack Overflow

Sort columns using VBA from newest to oldest

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related