Sort Already Selected Range

[AWiseGuy]

I have a spreadsheet with multiple parts on one sheet. Each part has a different number of rows, but the key column is always the same.

I want to be able to highlight the relevant range, then invoke a macro to sort that range. Then I want to highlight another range and invoke the macro to sort only that range, etc.

This is a mock up of what I'm looking to do. I want to highlight a range (Task through last Hours), then invoke the macro to sort by Hours.

Site 1
Task	Assigned	Hours
Job1	Smith	25
Job2	Jones	32
Job3	Silver	15
Site 2
Task	Assigned	Hours
Job1	Smith	25
Job2	Jones	32
Job3	Silver	15
Job 4	Wilson	8
Job 5	Smith	12
Site 3
Task	Assigned	Hours
Job1	Smith	25
Job2	Jones	32

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[Gra06021]

Sub Macro1()
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range(Active)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

You can almost do this just as fast by just knowing the quick commands:
ctrl + a to select the range
alt + a + s + s to sort the data

 

[AWiseGuy]

Couldn't get the quick commands to work, but the macro fails on the .SetRange Range(Active) command - it doesn't like the keyword Active. I don't think it matters , but if it makes a difference, I'm using Excel 2010

 

[MARK858]

Assuming that you are selecting from the word Task to the last number in the Hours column in each section.

Sub SortRangeX()
  Selection.Sort Key1:=Selection.Cells(1, 3), Header:=xlYes
End Sub

or if as per your sample there is a blank row between each section then select a single cell in the section and run...

Sub SortRangeZ()
    With ActiveCell.CurrentRegion
        .Offset(1).Resize(.Rows.Count - 1).Sort Key1:=.Cells(1, 3), Header:=xlYes
    End With
End Sub

 

[AWiseGuy]

Mark858's first code option works perfectly - now let me add a small wrinkle. There is a blank column in the data (as shown below). I'd like to put the cursor in the top left hand cell and have the macro do the equivalent of {End Down}, {Right 4} to select the range, then I'll execute the sort step as is. I've tried using Offset, but I can't get it to work.

Task	Assigned	Hours		Shift
Job1	Smith	25		Day
Job2	Jones	32		Day
Job3	Silver	15		Night

<tbody>
</tbody>

 

[AWiseGuy]

Got it figured out - code below in case anyone else has this need. Thanks for the help...

Sub SortSelected()


With ActiveSheet
Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 10)).Select
Selection.Sort Key1:=Selection.Cells(1, 3), Header:=xlYes
End With


End Sub

 

[MARK858]

Is there the Site 1, Site 2 etc. in the immediate line above each section as you showed in your first post?

Is there a blank row between each section?

How many columns are there in reality? you showed 4 originally, your last post shows 5 including the blank column but you are offsetting by 10?

 

[AWiseGuy]

The actual data is set up in a similar fashion as the first post with blank lines between each section that needs to be sorted.
My examples only showed 4/5 columns to simplify things. In actuality there are more (as indicated by the Offset).

Start with the cursor in the header row of the first column to be sorted. This presumes no blank lines in the data table. The number in OFFSET is set to the number of columns to be sorted.
Range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 10)).Select
This statements presumes the key column for the sort is the 3rd one. To sort of another column simply change 3 to {whatever}.
Selection.Sort Key1:=Selection.Cells(1, 3), Header:=xlYes

 

[MARK858]

Are the number of columns 11 as in your last post or can they vary up or down?
and the same question as previously is there a cell with "Site" and a number above each section? as in your first post.

Please try and answer the questions as asked.

 

[AWiseGuy]

Sorry, I thought I had answered the questions.

There are an unknown number of contiguous rows [End(xlDown)], and exactly 10 columns in my dataset with one blank column in the middle [OFFSET(0,10)].
There is a title block above the header row to identify each block of data ("Site"). By putting the cursor on the header row below that title, I keep it outside the sort parameters.