Commence macro from current (not absolute) cursor position

[BlackieHamel]

I should know how to do this, but I don't.

When I use Record Macro to create some new VBA code, it records the absolute position - row, column, and sheet name - but I often want a macro to work anywhere from the starting position. See the code below. My goal is to select a set of cells in a column, run the macro, and have those cells sorted Largest to Smallest:

Sub Sort_returns_boxes()
'
' Sort_returns_boxes Macro
' Sort the selected column from highest to lowest without affecting other columns in the spreadsheet.
'
' Keyboard Shortcut: Ctrl+s
'
    ActiveWorkbook.Worksheets("""Periodic Table""").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("""Periodic Table""").Sort.SortFields.Add2 Key:= _
        Range("N26:N34"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("""Periodic Table""").Sort
        .SetRange Range("N26:N34")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

How do I make this relative rather than absolute positioning? Thanks -- Blackie

 

[Fluff]

Change

 Range("N26:N34")

to

Selection

 

[BlackieHamel]

Fluff -- thanks, but that didn't work. Note that "Range ("N6:N34")" appears twice in the code. Do I replace it with "Selection" (and not Range:Selection) in both places? And can I safely delete the lines that make reference to

ActiveWorkbook.Worksheets("""Periodic Table""")​

 

[Fluff]

Change both instances of the range with selection, the periodic table sheet will need to be the active sheet.

 

[BlackieHamel]

I think I'm close -- it worked once! -- but now I get a 1004 error: "the Sort reference is not valid." Can you see what's wrong with this code?

Sub Sort_returns_boxes()
'
' Sort_returns_boxes Macro
' Sort the selected column from highest to lowest without affecting other columns in the spreadsheet.
'
' Keyboard Shortcut: Ctrl+n
'
    
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add2 Key:= _
        Selection, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Selection
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Fluff]

Firstly you have removed the line that clears the sort field, you need to put that back in, otherwise you are likely to get into trouble.

 

[Fluff]

Another option is

Sub BlackieHamel()
   Selection.Sort key1:=Selection, order1:=xlDescending, Header:=xlNo, dataoption1:=xlSortNormal
End Sub

 

[BlackieHamel]

That was it! Working great now. Thanks, Fluff!
Blackie

 

[Fluff]

You're welcome & thanks for the feedback