Dynamic Table Auto-Sort Using VB

Beard_Acc

New Member
Joined
May 16, 2020
Messages
2
Office Version
2019, 2013, 2010
Platform
Windows
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,263
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:
VBA Code:
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

New Member
Joined
May 16, 2020
Messages
2
Office Version
2019, 2013, 2010
Platform
Windows
Thanks so much for this, I just tried it out and it works perfectly!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,113
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top