Repeat Active Filter After Userform Entry

Question

I have a macro that sorts clients based off of "Contact Dates", "Deposits", and "Credit". Say I select to sort by "Contact Date" and then I add a new client to my list, how would can I have the active sort re-run after I have entered a new client from my user form?

Userform I use to add client data

Filter Options

Here is my code:

Credit Balance Sort

Sub creditbalance()

    Dim w As Long, lr As Long, wss As Variant

    wss = Array("contactunder")


    For w = LBound(wss) To UBound(wss)
        With ThisWorkbook.Worksheets(wss(w))
            lr = Application.Max(.Cells(.Rows.Count, "a").End(xlUp).Row, _
                                 .Cells(.Rows.Count, "da").End(xlUp).Row)
            With .Range(.Cells(10, "a"), .Cells(lr, "da"))
                .Cells.Sort Key1:=.Columns(97), Order1:=xlDescending, _
                            Orientation:=xlTopToBottom, Header:=xlYes
            End With
        End With
    Next w

End Sub

Contact Date Sort

Sub contactdate()
    Dim w As Long, lr As Long, wss As Variant

    wss = Array("contactunder")


    For w = LBound(wss) To UBound(wss)
        With ThisWorkbook.Worksheets(wss(w))
            lr = Application.Max(.Cells(.Rows.Count, "a").End(xlUp).Row, _
                                 .Cells(.Rows.Count, "da").End(xlUp).Row)
            With .Range(.Cells(10, "a"), .Cells(lr, "da"))
                .Cells.Sort Key1:=.Columns(2), Order1:=xlDescending, _
                            Orientation:=xlTopToBottom, Header:=xlYes
            End With
        End With
    Next w

End Sub

Deposit Balance Sort

Sub depositbalance()
    Dim w As Long, lr As Long, wss As Variant

    wss = Array("contactunder")


    For w = LBound(wss) To UBound(wss)
        With ThisWorkbook.Worksheets(wss(w))
            lr = Application.Max(.Cells(.Rows.Count, "a").End(xlUp).Row, _
                                 .Cells(.Rows.Count, "da").End(xlUp).Row)
            With .Range(.Cells(10, "a"), .Cells(lr, "da"))
                .Cells.Sort Key1:=.Columns(68), Order1:=xlDescending, _
                            Orientation:=xlTopToBottom, Header:=xlYes
            End With
        End With
    Next w
End Sub

Answer 1

The little code you've shown is very redundant - by making the hard-coded Key1 sort argument a parameter, you instantly remove the need for two of these three clones, and repurpose the third to do the job for all three.

Sorting and applying a sort, is something very easy to do when your range is a ListObject aka "table". Take your range, select "format as table" from the Home Ribbon. Now you never need to work out the last row ever again.

Also, if the wss(w) sheet exists in ThisWorkbook at compile-time, there's no reason to dereference it from the Worksheets collection - just use its code name identifier (you can change it by selecting the sheet in the Project Explorer / Ctrl+R, then changing its (Name) property in the Properties toolwindow / F4) - then you can do TheSheetName.Range("whatever"). Or better - since that code only ever needs to work on a specific sheet, put it in that sheet's code-behind, and use Me to refer to the Worksheet instance:

Public Sub ApplySortOrder(Optional ByVal sortColumn As String = vbNullString)

    With Me.ListObjects(1)

        Dim sortColumnRange As Range
        If sortColumn <> vbNullString Then
            'assumes sortColumn is an existing column header
            Set sortColumnRange = .ListColumns(sortColumn).DataBodyRange
        End If
        With .Sort
            If Not sortColumnRange Is Nothing Then
                .SortFields.Clear
                .SortFields.Add sortColumnRange
            End If
            .Apply
        End With
    End With

End Sub

Now, assuming I got the assumed column headings right, the code you have that's calling depositbalance can look like this:

TheSheetName.ApplySortOrder "DepositBalance"

Sorting by contactdate would be this:

TheSheetName.ApplySortOrder "ContactDate"

Sorting by creditbalance:

TheSheetName.ApplySortOrder "CreditBalance"

And if you want to re-apply the current sorting:

TheSheetName.ApplySortOrder

And the day you need to sort by something else, you can just do:

TheSheetName.ApplySortOrder "ThatFancyNewColumn"

And be done with it, without needing to copy-paste yet another procedure.

You could even declare a Public Enum for the valid columns...

Public Enum SortingColumn
    Current = 0
    CreditBalance = 97
    DepositBalance = 68
    ContactDate = 2
End Enum

Then change the signature to accept a SortingColumn parameter:

Public Sub ApplySortOrder(Optional ByVal sortColumn As SortingColumn = Current)

    With Me.ListObjects(1)

        Dim sortColumnRange As Range
        If sortColumn <> Current Then
            'assumes sortColumn is an existing column header
            Set sortColumnRange = .ListColumns(sortColumn).DataBodyRange
        End If
        With .Sort
            If Not sortColumnRange Is Nothing Then
                .SortFields.Clear
                .SortFields.Add sortColumnRange
            End If
            .Apply
        End With
    End With

End Sub

Or even better, leave out the explicit enum values, and map each value to a string column name - then write a function that gets you the ListColumn.Index for it, so the user can't rename the headings, but they can still move these 90-something columns as they please. ...but that's for another post I guess.