sort macro with a twist

[ddoyle67]

I am currently using the following macro assigned to a command button to sort a list of jobs by the due date. I have others that sort by Customer and Job #. It works great for what it was originally written for. Sometimes(more now due to Covid) we have jobs that sit in the shop past their due dates and would like to list them at the bottom when sorted and not the top like they do with the current way it sorts so we are looking at current jobs first.

Sub Due_Date()
'
' Due_Date Macro
'

'
Range("A4:W75").Select
ActiveWorkbook.Worksheets("Job Board").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Job Board").Sort.SortFields.Add2 Key:=Range( _
"F5:F75"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Job Board").Sort
.SetRange Range("A4:W75")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A5").Select

End Sub

I was thinking it would be something like "If F is < O2 (Cell which displays the current date) then move that to the bottom of the list. " the exact format is where I fall flat.

I appreciate any help with this.

 

[Yongle]

I think you are wanting a double sort in the same column and that is not possible

Try add another column and sorting based on that column intsead
formula in row2 copied down
=IF(F2>=TODAY(),F2,"Bottom")

 

[ddoyle67]

So the other column would only have due dates in the past then sort by that column to place them at the end? Does that formula go in my VBA code? Or do I need a second due date filter? I dont want these at the end all the time only when I sort the jobs by due date.

 

[Yongle]

Test on a COPY of your workbook

The code assumes there is nothing below the data
- code finds the last automatically based on last found value in column A
- let me know if that is an incorrect assumption

Sub Due_Date()
    Dim rng As Range, lr As Long, r As Long, F As Date
    With ActiveWorkbook.Worksheets("Job Board")
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rng = .Range("A4:A" & lr)
'insert value in column X
        For r = 5 To lr
            F = .Cells(r, "F").Value
            If F > Date Then .Cells(r, "X").Value = "BOTTOM" Else .Cells(r, "X").Value = F
        Next r
'sort on column X
        Set rng = rng.Resize(, 24)
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=.Range("X5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange rng
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
'clean up
         .Sort.SortFields.Clear
        .Columns("X").Clear
    End With
End Sub

 

[ddoyle67]

Your assumption is correct we are sorting the entire worksheet so nothing is below.

Thanks I will try and play with it over the weekend. This is live so I always work on a copy always trying to tweak something. Dont need to risk breaking it as I tinker around with it.

 

[Yongle]

Late thought ...
If code in post#4 does what you want .. I will post revised code so that it leaves a few empty columns in case you decide to use column X etc in the future