Excel Macro - run on selected rows

Question

Hi - I'm trying to set a macro to sort a selected range of rows by date (column E). It's running on the rows I used to record the macro. What do I need to change to have it apply to the rows I've selected when I run the macro? I'm on Excel desktop on a Mac. Thanks!

Here is the code:

'

' Macro4 Macro

'

'

    ActiveWorkbook.Worksheets("Big KS Comms List").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Big KS Comms List").Sort.SortFields.Add2 Key:= _

        Range("E2:E8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    With ActiveWorkbook.Worksheets("Big KS Comms List").Sort

        .SetRange Range("A2:P8")

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

End Sub

Answer 1

I tried reinstalling the macro, and it still doesn't work in my file.

Susanna,

your dates are in column D, not in column E as you shown above!

Change the line
Area.Sort Range("E1"), xlAscending, Header:=xlNo

to
Area.Sort Range("D1"), xlAscending, Header:=xlNo

done.

Andreas.

Answer 2

Thanks - still not working.

It is sorting one selected range thusly -- putting 7/15 first:

7/15/24
7/15/24
7/9/24
7/8/24
7/12/24
7/17/24
7/12/24
7/31/24
TBD

I put a test range lower down and tried, but got the message "Select some cells inside the data. Aborted"

Answer 3

Right-click on the sheet tab
Choose "View Code"
Within the menu click Insert \ Module
Paste in the code below
Close the VBA editor
Press Alt-F8
Choose the macro
Click Run

As shown, you can select one or multiple areas as you like.

Andreas.

Sub SortSelectedByDueDate()
Const Title = "SortSelectedByDueDate"
Dim Where As Range, Area As Range
Set Where = Intersect(Range("A1").CurrentRegion, Selection.EntireRow)
If Where Is Nothing Then
MsgBox "Select some cells inside the data. Aborted", vbInformation, Title
Exit Sub
End If
For Each Area In Where.Areas
If Area.Row = 1 Then
MsgBox "Do not select the headings! Aborted", vbExclamation, Title
Exit Sub
End If
Area.Sort Range("E1"), xlAscending, Header:=xlNo
Next
End Sub

Answer 4

Hmm, I don't quite understand your example.

My spreadsheet lists client (column A), project (column B), task (column c) -- and column E is due date. Screenshot below in case that helps.

I want to sort within each client/project by due date, earliest date first.

So I might select rows 2-8, and I want to hit the macro shortcut to sort those rows by due date (column E).

Then I want to be able to select rows 13-25 (different client/project) and use the macro to sort those rows by due date (column E).

I created the macro by "Record Macro," and it has recorded only for the specific rows I was using at that time. I want the macro to complete the action (sorting) but be agnostic to the rows -- so that it will complete the action on the rows I have selected when I run the macro.

Thank you!

Answer 5

You want to sort the columns A:P by column E of the selected rows!?

So if C2:F8,A12:A20 is selected, we sort A2:P8 and A12:P20 separately by column E without headings. Correct?

Andreas.