Macro to sort by specific column

[Darrell MacDonald]

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

 

[Anthony47]

Try this variant:

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]

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]

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]

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]

Your message still leave me with some doubts about how you plan to work, but let's try going on…
Try the following 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