Sort Ascending/Descending
I have two columns in a spreadsheet labeled Project No. and Project Name. As
users continually add entries to those columns, I want to create a button called "Sort by Project Number" at the top that toggles between ascending and descending order (in other words, if the Project No. column is already sorted in ascending order, then clicking the button sorts it in descending order, and vice versa). If it's not sorted, then sort it in ascending order. I know the sorting buttons exist on the toolbar, but these users need something simple and obvious, and doing it with one button would help. Thanks! Steve C |
Sort Ascending/Descending
Sub ABC()
Dim res As Variant Dim rng As Range Dim v As Long Dim ord As Long res = Application.Match("Project No.", Range("A1:IV1"), 0) if iserror(res) then msgbox "Column with header Project No. not found" exit sub end if Set rng = Range(Cells(2, res), Cells(Rows.Count, res).End(xlUp)) v = Application.Evaluate("Sumproduct(--(" & rng.Offset(1, 0) _ .Resize(rng.Count - 1, 1).Address(0, 0) & "=" & _ rng.Resize(rng.Count - 1, 1).Address(0, 0) & "))") ord = xlAscending If v = rng.Count - 1 Then ord = xlDescending End If rng.Resize(, 2).Sort _ Key1:=rng(1), _ Order1:=ord, _ Header:=xlNo End Sub worked for me. It assumes Project Name is to the left of Project No. -- Regards, Tom Ogilvy "Steve C" wrote: I have two columns in a spreadsheet labeled Project No. and Project Name. As users continually add entries to those columns, I want to create a button called "Sort by Project Number" at the top that toggles between ascending and descending order (in other words, if the Project No. column is already sorted in ascending order, then clicking the button sorts it in descending order, and vice versa). If it's not sorted, then sort it in ascending order. I know the sorting buttons exist on the toolbar, but these users need something simple and obvious, and doing it with one button would help. Thanks! Steve C |
Sort Ascending/Descending
Tom,
Thanks much for the code. It will be very useful! -- Steve C "Tom Ogilvy" wrote: Sub ABC() Dim res As Variant Dim rng As Range Dim v As Long Dim ord As Long res = Application.Match("Project No.", Range("A1:IV1"), 0) if iserror(res) then msgbox "Column with header Project No. not found" exit sub end if Set rng = Range(Cells(2, res), Cells(Rows.Count, res).End(xlUp)) v = Application.Evaluate("Sumproduct(--(" & rng.Offset(1, 0) _ .Resize(rng.Count - 1, 1).Address(0, 0) & "=" & _ rng.Resize(rng.Count - 1, 1).Address(0, 0) & "))") ord = xlAscending If v = rng.Count - 1 Then ord = xlDescending End If rng.Resize(, 2).Sort _ Key1:=rng(1), _ Order1:=ord, _ Header:=xlNo End Sub worked for me. It assumes Project Name is to the left of Project No. -- Regards, Tom Ogilvy "Steve C" wrote: I have two columns in a spreadsheet labeled Project No. and Project Name. As users continually add entries to those columns, I want to create a button called "Sort by Project Number" at the top that toggles between ascending and descending order (in other words, if the Project No. column is already sorted in ascending order, then clicking the button sorts it in descending order, and vice versa). If it's not sorted, then sort it in ascending order. I know the sorting buttons exist on the toolbar, but these users need something simple and obvious, and doing it with one button would help. Thanks! Steve C |
All times are GMT +1. The time now is 03:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com