Auto sort excel sheet

[RonB]

Hi, is it possible to set up an auto sort function in excel 2002? I would like to have my spread sheet auto sort after each new row is complete. I would like it to sort based on the results of one column. For example: column A would contain text, column B would contain text, column C would contain a number. I would like to sort desending on column C. If it is possible, could you write the macro for me, so I can copy and paste. I am not familiar with setting up macros in excel.

Thanks

 

[MI man]

Goto VIEW tab on the menu bar, check on FORMS..You would see a tool bar popup), click on CommandButton icon and place the CommandButton at a desired place in your spreadsheet...you would get a dialog box related to macros...click on RECORD...then do all the tasks that you want to do (in this case, the SORT task)..then click on STOP icon...

Your macro is ready to use....

You can find it more clearly in the Excel Help...

 

[RonB]

Goto VIEW tab on the menu bar, check on FORMS..You would see a tool bar popup), click on CommandButton icon and place the CommandButton at a desired place in your spreadsheet...you would get a dialog box related to macros...click on RECORD...then do all the tasks that you want to do (in this case, the SORT task)..then click on STOP icon...

Your macro is ready to use....

You can find it more clearly in the Excel Help...

Is there a way to automate without using a command button. I would like to sort automatically without pushing any additional buttons? I also do not know how to write VB.

 

[MI man]

Goto TOOLS> MACRO...
There would be an option to record a macro....(may be NEW). Then a dialog box would appear to record macro....there would be an option to assign a shortcut key (Option would start with 'CTRL+' followed by a box..input any key (preferably, the ones that are not assigned by the office, like ctrl+c etc), for instance ctrl+q and record the macro as described in the previous post.
Then press ctrl+q to sort the data.
You do not need to write a code for it as the macro recorder would record all your actions for the task in the form of code.
However, if you wish to develop your skills, then do look into the IDE...
To view code, press Alt+F11 key to view the IDE and click on WORKBOOK>MODULE option in the explorer window of the IDE.

Hope am clear in my saying....

 

[Brianwarnock]

2 points
1 The macro recorder records for specific conditions and tends therefore to be only an initial guide. The cell range for instance is fixed
2 You are asking for the automation to be at a specific time that Excel cannot determine, therefore you would have to fire the macro in one of the ways MI man has indicated.

eg If you put the following code in the worksheet_change event it would run for any change to a cell, in the worksheet_activate event whenever the worksheet is selected.

ActiveSheet.UsedRange.Select
    Selection.Sort Key1:=Range("C1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

Brian

 

[RonB]

Thanks for your help MI and Brian, I tried MI's suggestions with the Macros and couldn't get them to work. I also tried to paste your code with no luck. I am completely in the dark regarding VB code. In the meantime I received another code suggestion that worked.

I appreciate you help,
Ron

 

[Brianwarnock]

Thanks for the update. I wouldn't mind seeing that suggestion.

Brian

 

[RonB]

Hi Brian,

Here is the code I received from my other source. It works exactly as planned. Thanks again for your help.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
For c = 3 To 11 Step 2
If Not Intersect(Columns(c), Target) Is Nothing Then
Range(Columns(c - 1), Columns(c)).Sort Key1:=Cells(1, c), Order1:=xlAscending, Header:=xlYes
End If
Next c
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Brianwarnock]

Thanks for posting Ron.

As expected that code runs after every cell change.
I don't understand the Step2, infact the whole For statement is confusing in what I was thinking of as your requirement, plus it only sorts column C not the whole data based on C which i assumed you required.

Of course 2007 which I don't have might behave differently, but weird.

Brian

 

[RonB]

Hi Brian, the project changed a bit from the original request. It ended as follows:

It used columns A, B,C, D,E, F,G, H,I, J,K

A is a number column that is not sorted
B is a name column which is sorted when C sorts
C is the ranking column which is sorted in ascending order
DE, FG, HI, JK sorts the same as B and C, but seperately from each other.

I can't explain the code, because I do not understand the logic. I got it from the Microsoft office web site forum. It took three tries, before we got the code we needed.

Ron

 

[Brianwarnock]

Thanks fot the update, I understand the code now, quite clever really.

Brian