Dynamic Table Auto-Sort Using VB

Beard_Acc

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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
Solution
Thanks so much for this, I just tried it out and it works perfectly!
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top