excel...how do you autosort a list

C

chiguy

looking for a BIT OF HELP HERE...

I NEED TO AUTO SORT A LIST OF CELLS EVERY MINUTE OR SO...

HOW DO I DO IT????

THANKS IN ADVANCE
 
You need to run a timed macro.

You can easily create the macro using the macro recorder.

The timer is a bit more trouble to program, but not brain surgery.
 
record the sorting macro from Tools-macro-record

open the macro's vba module: Tools-macro-edit

at the end of the macro's vba code, before End Sub, place this statement:

Application.OnTime (Now + TimeValue("00:01:00")) , "[insert macro's name here]"

good luck.

p.s. if this works ok, you may need a bit more code to clean up / stop running the macro when you close the worksheet.
 
Quote from chiguy:

looking for a BIT OF HELP HERE...

I NEED TO AUTO SORT A LIST OF CELLS EVERY MINUTE OR SO...

HOW DO I DO IT????

THANKS IN ADVANCE
More...

Here is a complete sample of the windows Declare function for the VBA Timer event and the VBA code itself in three parts...

Declares

Public Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long

Public Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, ByVal nIDEvent As Long) As Long

VBA Timer Code...

Sub StartTimer ()
On Error GoTo Err_Prob

TimerSeconds = 2 'pop the windows time every x seconds
TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)

Exit_Err_Prob:
Exit Sub

Err_Prob:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Err_Prob

End Sub

Sub EndTimer()
On Error GoTo Err_Prob

runTimer = False
KillTimer 0&, TimerID

Exit_Err_Prob:
Exit Sub

Err_Prob:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Err_Prob

End Sub

Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long)
On Error GoTo Err_Prob


DO YOUR CODE HERE TO SORT THE CELLS / RANGES YOU WANT TO..


'you also need error handling since pulling and pushing data burps once in awhile...

Exit_Err_Prob:
Exit Sub

Err_Prob:
If Err.Number = 55 Then
'take some code action here...
Resume Next
Else
Err.Clear ' Clear Err object fields
Resume Next
MsgBox Err.Description
End If
If Err.Number = 440 Or Err.Number = 432 Then
'Tell user what happened. Then clear the Err object.
MsgBox "There was an error attempting to open the Automation object!"
Err.Clear ' Clear Err object fields
Resume Next
End If
EndTimer
StartTimer
Debug.Print Err.Number & " " & Err.Description
Resume Exit_Err_Prob
End Sub

You build a macro button on the front interface of Excel that calls the start timer event and a button that does the End Timer event and -- bada bing... you got a timing system...

htHelps...

cj...


______________________
HAVE STOP - WILL TRADE

If You Have The Vision We Have The Code
 
Quote from stock777:

You need to run a timed macro.
...
More...

OR...

Keep two separate worksheets within the same workbook. Use one worksheet as the primary data worksheet and use the RANK() function to identify how your data is to be sorted via numbering (ie. 1, 2, 3, ..., N).

THEN

On the second worksheet, choose a column and number the cells 1...N. Use vlookup of the numbered column of this second worksheet to index the RANK() column of the first worksheet. This will sort your data in realtime continually.

I have an example workbook somewhere but it is on a different machine... It does not require any macro programming...
 
You must log in or register to reply here.
Back
Top