Auto Sorting in Excel 2010

[wolfrayz]

Hello,<o></o>
I am having a slight problem with an auto-sorting macro onan excel sheet. I have a sheet with aheader on row 2, and columns from A to Q, and the information on rows 3 to22. The issue is coming up from columnsB and C. I need the values in column Cto sort greatest to smallest, and then if there are matching values in column Cbut the values in B are different, I need the sheet to sort greatest tosmallest again.<o></o>
Example: In the simplified version of my table below, if the# of stocked Post-Its jumps to 15, I need that moved to the top, but if Reamsof Paper and Post-It’s have the same number of stocked items, but I need 13Reams of Paper, I need that to move above Post-Its. Any help will be greatly appreciated.<o></o>
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Names[/TD]
[TD]# of Item Needed[/TD]
[TD]# of Item Stocked[/TD]
[/TR]
[TR]
[TD]Tape[/TD]
[TD]16[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Post-Its[/TD]
[TD]12[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]Reams of Paper[/TD]
[TD]12[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Coffee Filters[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Can you sort it as you wish from the user interface? If so, what do you do?

 

[wolfrayz]

i have a custom sort on my user interface, but that requires an updated sort for every change in the values. i know there is a way to auto sort, i just don't know how to do it.

 

[shg]

Right. My question is, exactly how do you sort via the UI? Then it's easy to create a macro to do the same.

 

[wolfrayz]

Right now I go the to Sort & Filter button in the topright of the screen, go down to the custom sort, and at that point I have acustom sort that sorts A-Q based off column C greatest to smallest, and thencolumn B greatest to smallest. That is theentirety of my sorting knowledge.<o></o>

 

[shg]

Sub wolfrayz()
    With Range("A2", Cells(Rows.Count, "Q"))
        .Sort Key1:=.Range("C1"), Key2:=.Range("B1"), Header:=xlYes
    End With
End Sub

 

[shg]

Ah -- sort automatically.

Code must go in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("A2", Cells(Rows.Count, "Q"))
        If Not Intersect(Target, .Cells) Is Nothing Then
            .Sort Key1:=.Range("C1"), Key2:=.Range("B1"), Header:=xlYes
        End If
    End With
End Sub

 

[wolfrayz]

Wow, thats awsome. Is there a way to get it it from greatest to smallest, because the macro is doing smallest to greatest. Other wise it is working perfectly.

 

[shg]

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("A2", Cells(Rows.Count, "Q"))
        If Not Intersect(Target, .Cells) Is Nothing Then
            .Sort Key1:=.Range("C1"), Order1:=xlDescending, _
                  Key2:=.Range("B1"), Order2:=xlDescending, _
                  Header:=xlYes
        End If
    End With
End Sub

 

[wolfrayz]

That did the trick, thanks a ton.