Sort Ascending/Descending

S

Steve C

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
 
T

Tom Ogilvy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top