Solved

Repeating a sort in Excel

Posted on 2012-04-07
22
797 Views
Last Modified: 2013-01-08
I've upgraded from Excel 2003 to 2010.

I used to regularly repeat a sort by pressing the F4 key.

This no longer works in Excel 2010, and apparently in 2007.  Another step backwards for my money.

l've searched many forums and the answer always seems to confirm that the F4 repeat functionality on the sorting dialogue box has been taken away but I just wondered if you guys had any other thoughts or workarounds?   :-)

Thanks
0
Comment
Question by:Glen_Sydney
  • 11
  • 11
22 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37819146
Take a look at the last post at this link.
0
 

Author Comment

by:Glen_Sydney
ID: 37819181
Thanks, I did find that previously but it didn't work for me.

I sort a lot of columns (trips in bus timetables).

I sorted one sheet (by column) using my desired preferences.

Then I sorted that sheet (by column) using the A to Z button (which produces an irrelevant result because everything goes to the top of the page) then I hit CTRL+Z.

My original sort preferences are lost and the dialogue box has reverted to sorting by rows with no preferences entered.

So that didn't help, unless I am missing something in following those instructions??
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37819186
Could you record a macro while you sort and then use the macro for future sorts?
0
 

Author Comment

by:Glen_Sydney
ID: 37819206
Thankyou.

Yes I suppose I could if I had a lot of sorts to do using the same preferences (in terms of which rows I sort by first, which one second etc).

However in other cases where I just sort by, for example, rows 5, 12 and 15 a few times, then another day on a different sheet do a few sorts by rows 7, 14 and 17 etc I'm guessing I would need to record separate macros each time.

It sure was a lot easier when you could just hit the F4 button!
0
 

Author Comment

by:Glen_Sydney
ID: 37819214
Also I am sure it used to be that Excel would actually remember the last sort you did on a particular worksheet so you could come back some days later and use it again.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37819220
You could write a macro which looked for all the rows you previously select and have it use those rows to sort with.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37819265
I was just starting to experiment with a macro like the one I mentioned in my previous post and it makes me ask if you meant "columns" instead "rows" when you said

However in other cases where I just sort by, for example, rows 5, 12 and 15 a few times, then another day on a different sheet do a few sorts by rows 7, 14 and 17 etc I'm guessing I would need to record separate macros each time.

If you did mean rows I need more explanation of what you're doing.
0
 

Author Comment

by:Glen_Sydney
ID: 37819333
I do both.   Sometimes I sort by rows sometimes I sort by columns.   Above I was referring to sorting by rows.

As an example of sorting by columns sometimes I compile a list of bus departures on several bus routes.   I copy the times and route numbers of each route into rows on a sheet and sort firstly by the column containing the departure time and secondly by the column containing the route number.

So I end up with a list of bus times in time order but if two buses depart at the same time then the Route 1 bus will appear before the Route 2 bus.

(I just recorded a macro to do that sort but it became specific to the worksheet I recorded it on.  I need to learn more about how I can simply repeat the keystrokes on any worksheet that I select data on).

As an example of sorting by rows I have a bus timetable in which each column shows the time that each bus departs each stop and at the top of the column it shows which driver does each trip.

When I sort by rows I end up with the data ordered so that it shows me the trips run by each driver in sequence for that driver, i.e. all the trips by Driver 1 first, then all the trips by Driver 2 and so on.

Hope that helps!   :-)   Thank you for your efforts.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37819368
If the macro is in a Module it should be available to all the sheets in a workbook.

I'm still confused about sorting by rows. In my (2003) version of Excel I can only sort on a single, contiguous, group of rows. You seem to be saying that you can for example sort on rows 7, 14 and 17. Is that correct?
0
 

Author Comment

by:Glen_Sydney
ID: 37819426
Sorry it may be the way I am explaining it.

Yes I sort on a single, contiguous, group of rows.

I just mean when I set the Option to "sort left to right" I set the Sort options to
"Sort by" row 7
"then by" row 14
"then by" row 17

Thanks again.

(BTW it is 2am here so I will check any further replies tomorrow).
0
 

Author Comment

by:Glen_Sydney
ID: 38717650
Sorry it's been a while.  :-)

I'm back to my dilemma of how to sort by rows on a repetitive basis on many sheets within a file.

I have a lot of sheets where I have to sort by rows based on the values in column D.

I tried recording a macro but when I replayed it on the next sheet it went back to the sheet I recorded it on.

I just don't understand how to get the macro to replay on selected rows of the active sheet rather than going back to the sheet on which the macro was recorded.

I'd welcome any thoughts, and ..... btw ....... Merry Christmas too!

Many thanks
Regards
Glen
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Glen_Sydney
ID: 38717660
A bit more info.   Thanks!

The error I get is:

Run time error '9':
Subscript out of range

------------------------------------

Here is my macro.   My problem seems to be is that it refers to the sheet I recorded it on ("Glenfield DOWN") in the selected range there, but I want it to refer to any sheet with any range I choose.


Sub Sort_Inspectors_by_D()
'
' Sort_Inspectors_by_D Macro
' Sort_Inspectors_by_D
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.Worksheets("Glenfield DOWN").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Glenfield DOWN").Sort.SortFields.Add Key:=Range( _
        "D4:D329"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Glenfield DOWN").Sort.SortFields.Add Key:=Range( _
        "B4:B329"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Glenfield DOWN").Sort
        .SetRange Range("A4:I329")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38717668
You could record a macro that by way of an InputBox or two you could input the parameters that vary.
0
 

Author Comment

by:Glen_Sydney
ID: 38717689
Thanks  :-)    but I'm not very experienced with macros.

Is there a way I can record a macro that, for example, sorts by the same column (D) in my case, but will sort on any group of rows I select, of any number of rows, in any file or sheet?

I thought recording the macro with the 'Use Relative References' button on may help, but when I tried it on a blank sheet it only worked on that same sheet.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38718810
Substitute ActiveSheet (or the name of some target sheet) for the name of the sheet in the macro you recorded.

It would help if you posted the macro and described how you wanted it changed.
0
 

Author Comment

by:Glen_Sydney
ID: 38725637
Hi, thanks for your advice and sorry for the delay during the holiday period.

I just recorded the macro afresh and it is below:

This sorts the rows by the values in column D but when I try to run it on another sheet it refers back to the sheet I recorded it on ("Cabramatta").

Sub Rail_Inspector_Sort_by_Column_D()
'
' Rail_Inspector_Sort_by_Column_D Macro
' Rail_Inspector_Sort_by_Column_D
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.Worksheets("Cabramatta").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Cabramatta").Sort.SortFields.Add Key:=Range( _
        "D18:D49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Cabramatta").Sort
        .SetRange Range("A18:H49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


I then took your suggestion and substituted ActiveSheet for the name of the sheet (revised macro is below).

That improves things, thank you, except that the macro is still sorting only for the specific rows I originally selected when I recorded it.

I really want the macro to sort on any group of rows that I select, which can be any random number of rows in any sheet in any workbook.

How do I take out the specific rows references?   Sorry I'm unfamiliar with the syntax required.


Sub Rail_Inspector_Sort_by_Column_D()
'
' Rail_Inspector_Sort_by_Column_D Macro
' Rail_Inspector_Sort_by_Column_D
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "D18:D49"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("A18:H49")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub




Thank you for your help.
Regards
Glen
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38726196
Do something like this.

Dim startcell as string
Dim endcell as string

startcell = inputbox("Enter start cell")
Endcell = inputbox("Enter end cell")


Then instead of
SetRange Range("A18:H49")

You can do

SetRange Range(startcell & ":" & endcell)
0
 

Author Comment

by:Glen_Sydney
ID: 38749947
Thank you for that.

Sorry to sound so dumb :-) but do I put these lines .............

Dim startcell as string
Dim endcell as string

startcell = inputbox("Enter start cell")
Endcell = inputbox("Enter end cell")


................. at the beginning of my macro ahead of where I currently have:

   ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

The change to 'SetRange' I can figure out.

I did try to get the sequence right myself but it kept failing, although I did get the input boxes to work.

It's a shame though that I can't simply define the rows to be sorted by selecting any random group of rows like I used to do when the F4 key worked as a 'Repeat Sort'.

Thanks again and sorry this is taking so long for you.

Regards
Glen.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38751470
You can put the Dim-s anywhere before try to use them but it's pretty standard to put them at the top. The InputBox lines can be put anyplace after the Dim's but before you need to use startcell and endcell.

If you have any problems please show the whole macro.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38751515
How about this.


    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Selection.Columns(1), Order:=xlDescending
        .SetRange Selection
        .Apply
    End With

Open in new window

0
 

Author Comment

by:Glen_Sydney
ID: 38753699
Excellent, that is exactly what I needed.

Thank you so much.   :-)

I actually needed to sort by Column D and sort ascending but I was able to make those changes myself.

I even managed to set a second sort criteria by repeating the .SortFields line with a different column selection.

I really appreciate your help.

Regards
Glen
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38754799
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Modifying Conditional Format from VBA code 3 33
cannot get subtotal to work 8 18
VBA in SharePoint 3 19
TT Status Chang 3 32
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now