Sorting by VBA buttons

paddydive

Active Member
Joined
Jun 30, 2010
Messages
460
Hi all,

I want to know is it possible to assign a button in excel which will sort a range in ascending or decending order.

Say I have a range A5:C25, i want to insert two button in excel one for ascending and one for decending. Whenever a button is pressed it should sort the data on Column B accordingly.

Please help.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What type of data is stored in this COL? i.e. Text or Numbers...

I have a macro you can use that will alternate Ascending / Descending with the same button..

i.e. if the list is already ascending, it will flip it to descending. Press again and it will switch back to ascending... That way you only need one button. However the code is a little different depending on whether the values are numerical or text.

Let me know which your using and i will post the appropriate code.
 
Upvote 0
There are 2 icons in the menu, A-Z (sort ascending) and Z-A (sort descending).

Why wouldn't you use these? They are always available, in every Excel sheet, contrary to your 2 buttons with VBA code.
 
Upvote 0
Code:
Sub SortValues()
On Error Resume Next
Dim StartRow As Integer, myCol As Integer, Ctr As Integer, EndRow As Integer
Dim mySheet As Worksheet
Dim SortedA As Boolean
Dim myValue As Integer 'May need to change to match your data type.
Dim SortRange As Range, Key As Range
Set mySheet = Sheet1 'Your Worksheet
Set SortRange = mySheet.Range("A1:A20") ' Entire Dtable you want to sort
Set Key = mySheet.Range("A1:A20") ' Column to sort by
StartRow = 1 'Row data begins on
EndRow = StartRow + 10 'This number needs to be less than your total number of rows... Does NOT need to be larger than 10, but can be smaller if your data source is small.
myCol = Key.Column
Ctr = 0
SortedA = False
For r = StartRow To EndRow
myValue = mySheet.Cells(r, myCol).Value
If myValue < Ctr Then
SortedA = True
GoTo 1
End If
Ctr = myValue
Next

mySheet.Sort.SortFields.Clear
mySheet.Sort.SortFields.Add Key:=Key, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With mySheet.Sort
    .SetRange SortRange
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Exit Sub
 
1
mySheet.Sort.SortFields.Clear
mySheet.Sort.SortFields.Add Key:=Key, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With mySheet.Sort
    .SetRange SortRange
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
 
Upvote 0
Well done Sthrncali.

But paddydive, do you really mean you create buttons to sort a column of entries alphabetically? What about Excel's 2 icons under "Data" in the menu?
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,711
Members
452,939
Latest member
WCrawford

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
Back
Top