De-selecting cells in a sheet processed indirectly that VBA has selected itself

[Bill Hamilton]

I have a short macro that is invoked from a button on a 'master' sheet which is the active sheet. The macro receives a reference to another worksheet as a parameter, works out an area to sort on that sheet, sorts it and exits. The sorted sheet is never activated or selected or referenced directly.

On exit, the sort area on that sheet is 'selected'. That is not done by the code. How can I de-select it and set the selected or active cell for the sheet somewhere else? I obviously can't actually directly select a cell or anything as it is not the active sheet, but there must be a way of getting rid of the big blue area in the middle of the screen which would annoy my user a lot when he come to look at that sheet if I had to leave it like that.

Hope someone can help.

 

[Andrew Poulsom]

What version of Excel do you have. The selection is unchanged if I run this while another worksheet is active in Excel 2003:

Sub Test()
    With Worksheets("Sheet3")
        .Range("A1:B4").Sort Key1:=.Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With
End Sub

 

[Bill Hamilton]

Thanks for the speedy reply Andrew.

I'm using 2007 - sorry, should have mentioned that. Here's my code, all stripped down to the bare essentials. The original goes through a few hoops working out the area to be sorted but these specific values do the biz.

Sub Test()
With Sheets("Class A comp 1")
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=Range("C6"), SortOn:=xlSortOnValues, _
        Order:=xlAscending, DataOption:=xlSortNormal
  With .Sort
    .SetRange Range("C6:K20")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
End With
End Sub

On running this, the sort area C6:K20 is highlighted on completion.

Bill

 

[Andrew Poulsom]

What happens if you use the Sort method rather than Sort object (like I did)?

 

[Bill Hamilton]

Thanks. That worked, and I'll incorporate it into my project.

So, how's a body to know about the Sort Method then? All my sorting code originates from the Macro Recorder because it's just too fiddly to remember all its ins and outs. The 2007 Recorder always utilises what you're calling the Sort Object and I was under the impression that the 'Method' method from older versions of Excel was deprecated. What's Microsoft's agenda here? What's the point of having two different ways of doing the same thing? (or rather, not the same thing in this case.) Sorry, rant over. No reply required.

Anyway, thank you again,

Bill

 

[jimrward]

hi Bill

in doing the same sort of thing, i did away with sort many years ago and use RANK, MATCH, and INDEX to get results sorted, I could then add to the ranking to resolve ties on count back etc etc