# I need an AUTO-SORT MACRO IN EXCEL



## aPPLE_PI

Hello All!

I just joined today: I searched the web for HOURS and finally found a post on this site that is similar to what I need to do. That post is here: 

http://www.techsupportforum.com/f57/can-someone-help-me-auto-sort-in-excel-252123.html

I'm a complete newb so I hope you guys have the solution!

OK..here's the problem:

I have an Excel (2007) workbook with a single sheet (Sheet 1). There are hundreds of rows but only 5 coulmns (A - E). 

What I need is very simple. We're at a plant and we have an industrial control process that monitors temperatures. The outputs stream into the excel workbook ever few seconds. 

I want to "Sort" this sheet by "Column E." This is, of course, very easy to do: simply highlight columns A through E and select "custom sort." (The values are always numerical only).

So what's the problem? The problem is that the numbers in the cells of Coulumn E change every 30 seconds or so. That means that I would have to manually sort the sheet by Column E every 30 seconds.

I want a way for Excel to AUTOMATICALLY re-sort the sheet (from highest to lowest) every time time the numbers in Column E change (which could be every 30 seconds or even every few seconds). 

I hope you guys can help!!!


----------



## David M58

You can take the code example I posted in the thread you referenced in your post, and adapt it to your situation.



Code:


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Worksheets("Sheet1").Range("E:E"), Target) Is Nothing) Then
        DoSort
    End If
End Sub

Private Sub DoSort()
    Worksheets("Sheet1").Range("A:E").Sort Key1:=Worksheets("Sheet1").Range("E1"), Order1:=xlDescending, Header:=xlYes
End Sub

This code sorts the data in columns A through E, by column E (descending), when any value in column E is changed. Modify as necessary.


----------



## aPPLE_PI

This works perfectly!!! Thank you for your help!!! :tongue: :wave: :tongue: :tongue:


----------



## aPPLE_PI

Actually, I'm having major problems. Here's the situation:

I want to sort several columns, from highest to lowest, based on Column B. 

Here is the macro for that:


*Sub Sort_Column_B()
'
' Sort_Column_B Macro
' Macro recorded 6/18/2009
'
'
Columns("A:G").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, 
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub*


Now, I want this to run continuously. To do this, I right-clicked on "Sheet 1," selected VIEW CODE, and pasted this:


*Dim Va1val
Private Sub Worksheet_Calculate()
If Va1val <> Range("B1") Then
Va1val = Range("B1")
Sort_Column_B
End If
End Sub *


This did not work and caused my system to crash. I think this is because each cell in Column B contains a formula that refers to other columns. I DID finally get it to work by replacing the above with this:


*Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Sort_Column_B
Application.EnableEvents = True
End Sub*

Problem now is.....my video card literally burned out and I had to replace it!!!! (This code seemed to refresh the "sort" macro every milli-second, and the system just couldn't keep up.) 


To prevent this onslaught on my computer, what I need to do now is refresh the sorting of Column B, say, every 15 seconds instead. Being computer-pubescent, I am having a hard time understanding. I went to a web link and got the following advice:


*"....Declare Public variables in a standard code module, outside of and before any procedure (Sub or Function) declaration: *

_(I have NO CLUE what the above means)_

*Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub" ' the name of the procedure to run*


_(Do I just right-click on Sheet 1 [VIEW CODE], and paste the above in? I guess my question is: where do i put this code?)_


*To start a repeatable timer, create a procedure named StartTimer as shown below: *
*Sub StartTimer()
RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub*

_(Do I just right-click on Sheet 1 [VIEW CODE], and paste the above in....or do I just create a new macro and paste this in?)_

*This stores the time to run the procedure in the variable RunWhen, two minutes after the current time. Next, you need to write the procedure that will be called by OnTime. 

For example,
Sub TheSub()
''''''''''''''''''''''''
' Your code here
''''''''''''''''''''''''
StartTimer ' Reschedule the procedure
End Sub.*

_(Where it says "your code here," do I just put the code for my sorting macro? And this is just a macro itself, right?)_



Like I said, *I only want to be sort about every 15 seconds*, without hindering the real-time data feed into the other columns. And also, Column B contains formulas. I don't mind starting this "sorting operation" of Column B manually...each time I open the workbook. And I don't mind stopping it manually each time I close the workbook. I don't need it to start or stop automatically, but merely to work right when I DO start it...and to run (semi-)continuously, re-sorting the column every 15 seconds.

I'm OVERWHELMED by all this code and stuff!!


----------



## ShosMeister

First off, is this the same problem as in the original post? If so, why are you trying to sort every 15 seconds if the data is being updated every 30?

That said, let's see what we can do here.


aPPLE_PI said:


> To prevent this onslaught on my computer, what I need to do now is refresh the sorting of Column B, say, every 15 seconds instead. Being computer-pubescent, I am having a hard time understanding. I went to a web link and got the following advice:
> 
> *"....Declare Public variables in a standard code module, outside of and before any procedure (Sub or Function) declaration: *
> 
> _(I have NO CLUE what the above means)_


Okay, public and private variables. You obviously know what variables are and the terms public and private mean just that. A public variable is available to everything and private is only available within the range of its declaration. The Va1Val variable that you used is in fact a public since it is DIMmed outside of the procedure Worksheet_Calculate(). That's all it means.



> *Public RunWhen As Double
> Public Const cRunIntervalSeconds = 120 ' two minutes
> Public Const cRunWhat = "TheSub" ' the name of the procedure to run*
> 
> 
> _(Do I just right-click on Sheet 1 [VIEW CODE], and paste the above in? I guess my question is: where do i put this code?)_


I would put it in a module rather than on the worksheet itself. Also, all of the timer code should go there as well - just my opinion on that.

I would still recommend against sorting every 15 seconds, but ... try this:



Code:


Put this in the workbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  StopTimer()
End Sub

Private Sub Workbook_Open()
  StartTimer()
  Application.OnTime Now + TimeValue("00:00:15"), "AutoSort"
End Sub

Now in any Standard Module (Insert>Module) place this;
Public RunWhen As Double
Public Const cRunIntervalSeconds = 15
Public Const cRunWhat = "AutoSort" ' the name of the procedure to run

Sub StartTimer()
  RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
  Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub

Sub StopTimer()
  Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub

Sub AutoSort()
  RunWhen=Now + TimeSerial(0, 0, cRunIntervalSeconds)
  Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
  Sort_Column_B()
End Sub


----------



## aPPLE_PI

First,...Thankyou very much for your assistance with this.

No dice, so far.

I put the 1st part of your code in "Sheet1 > Right-Vlick > View Code."

I inserted the 2nd part into a module like you said. I then went to menu bar and selected "Run > Continue."

An error code popped up saying: _"Compile Error: Syantax Error," _on this sub:



*Sub AutoSort()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
Sort_Column_B()
End Sub*


_Sub AutoSort()_ was highlighted yellow with an arrow next to it and "Sort_Column_B()" was in red text.

I checked numerous times for spelling errors but I suspect some piece of code is merely transposed or in the wrong place.

I never did insert these lines:

*Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "TheSub" ' the name of the procedure to run*


----------



## ShosMeister

You need to have the variables in there. If they are not, that's probably why. If there is a syntax error, I apologize but I just typed it out by hand and not inside Excel so check to make sure I didn't miss or add a comma or () somewhere.

A sample sheet may help if you still can't get it going.


----------



## aPPLE_PI

I have attached the workbook I am using. Thanx immensely for your help.


----------



## ShosMeister

You're running 2007 I see. Could you save it as a 2003 file and I'll try to open it and take a look. Is all of the timing code in there and just not working? If not, make sure it is in there. Try to get it working first and let us know if you run into an error what the error is.


----------

