Excel - sorting rows by balance due

To automatically sort rows so that rows with an open balance stay on top and rows with a balance of $0 move to the bottom, you can use a combination of Excel functions.

While Excel formulas alone will not automatically re-sort the data, you can use the following approaches to achieve your goal.

Approaches such as using the FILTER function or Use Conditional Sorting with VBA (Automatic Sorting) or Using Power Query (Semi-Automated).

Here is an example using VBA.

To have the rows sort automatically as you update the "Total Owed" column, you can use a VBA macro. Here’s a simple macro that sorts the data whenever there is a change in the worksheet:

1. Open the VBA Editor:

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

1. Insert the Macro:

• Double-click on the sheet where your data is (e.g., Sheet1) in the Project Explorer.
• Paste the following code into the code window:

Vba Code is untested backup your file first

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("YourColumnRange")) Is Nothing Then
        Application.EnableEvents = False
        Me.Range("YourDataRange").Sort Key1:=Me.Range("LastColumnHeader"), Order1:=xlDescending, Header:=xlYes
        Application.EnableEvents = True
    End If
End Sub

Replace YourColumnRange with the actual range for the "Total Owed" column (e.g., J:J if it's in column J).

• Replace YourDataRange with the range of the entire data table (e.g., A1:J1000).
• Replace LastColumnHeader with the specific cell reference for the header of the "Total Owed" column (e.g., J1).

1. Save and Close:

• Save the macro and close the VBA editor.

My answers are voluntary and without guarantee!

Hope this will help you.

NikolinoDE

I know I don't know anything (Socrates)