I'm curious to find out if I can eliminate the Range on the below macro I
created?

I want to it to not care what Range I select, as long as the Range includes
cells from column AN (Not the entire column). Since Excel 2007 does not save
the sort parameters I need to figure out a way to automate the sort. I have
several worksheets and several seperate ranges I need to sort on. Is this
possible? I want to run the macro AFTER I select the Range I want to sort
and not have the macro select the Range.

Sub SortByColumAN()
'
' Sorts data by column AN
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Add
Key:=Range( _
"AN54:AN58"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Renewal Rate").Sort
.SetRange Range("E54:DF58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Test with
If Not Intersect(ActiveWindow.RangeSelection, ActiveSheet.Range("N:N")) Is
Nothing

then pass ActiveWindow.RangeSelection as the range to sort

"FrankTimJr" wrote:

I'm curious to find out if I can eliminate the Range on the below macro I
created?

I want to it to not care what Range I select, as long as the Range includes
cells from column AN (Not the entire column). Since Excel 2007 does not save
the sort parameters I need to figure out a way to automate the sort. I have
several worksheets and several seperate ranges I need to sort on. Is this
possible? I want to run the macro AFTER I select the Range I want to sort
and not have the macro select the Range.

Sub SortByColumAN()
'
' Sorts data by column AN
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Add
Key:=Range( _
"AN54:AN58"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Renewal Rate").Sort
.SetRange Range("E54:DF58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

I don't completely understand how to add that code into the macro. I don't
know how to write code. Do I replace the code completely or add that line
somewhere within the existing code?


"Sheeloo" wrote:

Test with
If Not Intersect(ActiveWindow.RangeSelection, ActiveSheet.Range("N:N")) Is
Nothing

then pass ActiveWindow.RangeSelection as the range to sort

"FrankTimJr" wrote:

I'm curious to find out if I can eliminate the Range on the below macro I
created?

I want to it to not care what Range I select, as long as the Range includes
cells from column AN (Not the entire column). Since Excel 2007 does not save
the sort parameters I need to figure out a way to automate the sort. I have
several worksheets and several seperate ranges I need to sort on. Is this
possible? I want to run the macro AFTER I select the Range I want to sort
and not have the macro select the Range.

Sub SortByColumAN()
'
' Sorts data by column AN
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Add
Key:=Range( _
"AN54:AN58"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Renewal Rate").Sort
.SetRange Range("E54:DF58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Try (whole macro is given)
'(I am assuming headers are in Row 1)

Sub SortByColumAN()
'
' Sorts data by column AN
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Clear

If Not Intersect(ActiveWindow.RangeSelection, ActiveSheet.Range("AN:AN")) Is
Nothing Then
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Add _
Key:=Range("AN1:AN1"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Renewal Rate").Sort
..SetRange ActiveWindow.RangeSelection
..Header = xlGuess
..MatchCase = False
..Orientation = xlTopToBottom
..SortMethod = xlPinYin
..Apply
End With
Else
MsgBox "Col N is not included in Selection."
End If
End Sub


"FrankTimJr" wrote:

I don't completely understand how to add that code into the macro. I don't
know how to write code. Do I replace the code completely or add that line
somewhere within the existing code?


"Sheeloo" wrote:

Test with
If Not Intersect(ActiveWindow.RangeSelection, ActiveSheet.Range("N:N")) Is
Nothing

then pass ActiveWindow.RangeSelection as the range to sort

"FrankTimJr" wrote:

I'm curious to find out if I can eliminate the Range on the below macro I
created?

I want to it to not care what Range I select, as long as the Range includes
cells from column AN (Not the entire column). Since Excel 2007 does not save
the sort parameters I need to figure out a way to automate the sort. I have
several worksheets and several seperate ranges I need to sort on. Is this
possible? I want to run the macro AFTER I select the Range I want to sort
and not have the macro select the Range.

Sub SortByColumAN()
'
' Sorts data by column AN
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Add
Key:=Range( _
"AN54:AN58"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Renewal Rate").Sort
.SetRange Range("E54:DF58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

I like this technique from Debra Dalgleish's site:
http://contextures.com/xlSort02.html#Rectangles
Sort With Invisible Rectangles

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)


FrankTimJr wrote:

I'm curious to find out if I can eliminate the Range on the below macro I
created?

I want to it to not care what Range I select, as long as the Range includes
cells from column AN (Not the entire column). Since Excel 2007 does not save
the sort parameters I need to figure out a way to automate the sort. I have
several worksheets and several seperate ranges I need to sort on. Is this
possible? I want to run the macro AFTER I select the Range I want to sort
and not have the macro select the Range.

Sub SortByColumAN()
'
' Sorts data by column AN
' Keyboard Shortcut: Ctrl+Shift+Z
'
ActiveWorkbook.Worksheets("Renewal Rate").Sort.SortFields.Add
Key:=Range( _
"AN54:AN58"), SortOn:=xlSortOnValues, Order:=xlDescending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Renewal Rate").Sort
.SetRange Range("E54:DF58")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

--

Dave Peterson