Auto Sorting in Excel 2010

wolfrayz

New Member
Joined
Sep 4, 2013
Messages
5
Hello,<o:p></o:p>
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:p></o:p>
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:p></o:p>
Names# of Item Needed# of Item Stocked
Tape1614
Post-Its1211
Reams of Paper1210
Coffee Filters119

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Can you sort it as you wish from the user interface? If so, what do you do?
 

wolfrayz

New Member
Joined
Sep 4, 2013
Messages
5
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 4, 2013
Messages
5

ADVERTISEMENT

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:p></o:p>
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Code:
Sub wolfrayz()
    With Range("A2", Cells(Rows.Count, "Q"))
        .Sort Key1:=.Range("C1"), Key2:=.Range("B1"), Header:=xlYes
    End With
End Sub
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Ah -- sort automatically.

Code must go in the sheet module:

Code:
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

New Member
Joined
Sep 4, 2013
Messages
5
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,935
Messages
5,598,950
Members
414,268
Latest member
bluebandersnatch

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
Top