Macro to sort by specific column

Darrell MacDonald

New Member
Joined
Mar 25, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hello
I would like to create several macros to assign to ribbon icons, each of which is set to sort a selection by the column noted in the code for that button. For example, I would have buttons entitled A, B, C, etc. Button A would sort the selection by column A. Button B would sort the selection by column B, etc. I want the selected range to be dynamic and not set to a fixed range. When I record a macro for this, the code is as included below. Two of the lines in the code include a specific range ("A15:A24"). Can those lines be modified to just select the cells of the selection within the specific column?

Sub sortascendingbya()
'
' sortascendingbya Macro
'

'
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A15:A24" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A15:AC24")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Try this variant:
VBA Code:
Sub sortascendingbya()
' sortascendingbya Macro
Dim myRan As Range
'
Set myRan = Range("A15")                '<<<
'
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
Set myRan = Range(myRan, myRan.End(xlDown))
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=myRan _
  , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange myRan.Resize(, 29)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
This assumes that column A doesn't include empty cells
Then you have to "play" with the instruction marked <<<

Bye
 

Darrell MacDonald

New Member
Joined
Mar 25, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Thanks, Anthony, for taking the time. I tried it but it doesn't seem to work. I think the key part is the Range("A15") part.

Set myRan = Range("A15")

What might I do to that line when I "play" with it? I don't write code, unfortunately, so I'm a bit of a "babe in the woods" on this stuff.

I very much appreciate your willingness to help.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Probably I misinterpreted your request.
Are you talking about a single tabe to sort by different columns?

In this case the best approach is that the table be a "structured table"; in this way the table will resize by itself when you insert more lines, and the code of a recorded macro automatically refers to all the columns and all the rows.
If you need an overview about Tables, please have a look at Jan Karel Pieterse article here: Excel: Working with Tables

Bye
 

Darrell MacDonald

New Member
Joined
Mar 25, 2021
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hi Anthony
I guess I did a poor job of explaining what I'm looking for. Let me try to do a better job, with a scenario.

I have a worksheet with many columns and rows of data. I'd like to sort a user selected number of rows that isn't always the same. That's a variable, the selected rows. Once the rows are selected, I'd like to sort them by a specific column, also a variable. I'd like to have a batch of ribbon icons. Several would be labelled A, B, C, etc., indicating the column to sort the selected rows by. One group of these icons would be for sorting in ascending order and another for descending.

So, I would choose my rows and then hit the icon for which column to sort by.

I hope that made sense. Again, thanks so much for taking the time. It is much appreciated.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Your message still leave me with some doubts about how you plan to work, but let's try going on…
Try the following code:
Code:
Sub SortSel()
Dim cSel As Range, SortCol As Long
'
SortCol = 1         '<<< The column in the selection to sort by
'
Set cSel = Selection
If SortCol > cSel.Columns.Count Then Exit Sub
'
cSel.Parent.Sort.SortFields.Clear
cSel.Parent.Sort.SortFields.Add2 Key:= _
    Application.WorksheetFunction.Index(cSel, 0, SortCol), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With cSel.Parent.Sort
    .SetRange cSel
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub
This will sort the selected rows using the column set with SortCol =xx as the sorting column.
Then you can create new macro with different SortCol.

It works both if you select whole rows of data or specific ranges; in this second case, keep in mind that the SortCol is "relative" to the selected range, so if for example you select C10:M20 then SortCol=1 means "column C"

Bye
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,219
Members
417,131
Latest member
Seanr19871

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