Dynamic Table Auto-Sort Using VB

[Beard_Acc]

Hi all,

I am trying to perform an auto-sort of a table using VB, where the table is dynamic using formulae rather than hard-coded data that is static.

The table range is J3:R7 with row 3 being the table headers. When data is input, I need the table to update and sort based on the the updated formulae, with the sort precedent being column R (descending), column Q (descending) then column O (descending).

Can anyone supply the full code needed for this dynamic sort and include the command needed to automatically run the macro every 30 seconds, so I don't have to keep running the macro manually?

If it makes a difference, the input cells which the table formulae depends on, are on the same worksheet

Thanks,
J

 

[John_w]

Welcome to MrExcel forums.

First, record a macro to sort the table, e.g. Macro1.

When data is input, I need the table to update and sort

automatically run the macro every 30 seconds, so I don't have to keep running the macro manually?

You're asking for two different methods of calling the sort macro. For the first method, you want to sort the table when the input cells are changed. For the second method, you want to sort the table every 30 seconds, regardless of the input cells.

For the first method put this code in the sheet module, changing the range of input cells (e.g. A4:A7) as needed:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A4:A7")) Is Nothing Then
        Macro1
    End If
    
End Sub

For the second method have a look at Application.OnTime to run a macro periodically, e.g. every 30 seconds.

 

[Beard_Acc]

Thanks so much for this, I just tried it out and it works perfectly!