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.
[TABLE="width: 171"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Site 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Task[/TD]
[TD="align: center"]Assigned[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]Job1[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Job2[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TD="align: center"]Job3[/TD]
[TD="align: center"]Silver[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site 2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Task[/TD]
[TD="align: center"]Assigned[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]Job1[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Job2[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TD="align: center"]Job3[/TD]
[TD="align: center"]Silver[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]Job 4[/TD]
[TD="align: center"]Wilson[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]Job 5[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Site 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Task[/TD]
[TD="align: center"]Assigned[/TD]
[TD="align: center"]Hours[/TD]
[/TR]
[TR]
[TD="align: center"]Job1[/TD]
[TD="align: center"]Smith[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]Job2[/TD]
[TD="align: center"]Jones[/TD]
[TD="align: center"]32[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

Assuming exactly 10 columns in my dataset with one blank column in the middle means in English 11 columns and as stated there is a cell with "Site" above the "headers"

There is a title block above the header row to identify each block of data ("Site").

and a empty row between the areas

with blank lines between each section that needs to be sorted

then the code below will work if you select an cell in the area, as long as it is to the left of the empty column and it does away with the inefficient use of select/selection.

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

 

[jmishra91]

Apply .SetRange Selection

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

 

[john66]

Mark858 your a superstar your first suggestion was exactly what I have been looking for.