ChartMaster
New Member
- Joined
- May 22, 2009
- Messages
- 4
I am new to VBA and am trying to use the macro recorder to create the code to sort a range which can have a variable number of rows. I have tried using the relative addressing mode when recording the procedure, but the resulting code always uses the original range, ignoring additional rows when present. The macro recorder code is as follows:
Sub TestSort()
'
' TestSort Macro
'
' Keyboard Shortcut: Ctrl+s
'
Range("E2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 1).Range("A1:A17155"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:R17156")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
It seems that the range for sorting should have a relative addressing format, but it just "hard codes" the range that I used when doing the original macro recording.
I'm sure that I am missing something basic here, but I am stuck.
ChartMaster
Sub TestSort()
'
' TestSort Macro
'
' Keyboard Shortcut: Ctrl+s
'
Range("E2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=ActiveCell. _
Offset(0, 1).Range("A1:A17155"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet3").Sort
.SetRange ActiveCell.Offset(-1, 0).Range("A1:R17156")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
It seems that the range for sorting should have a relative addressing format, but it just "hard codes" the range that I used when doing the original macro recording.
I'm sure that I am missing something basic here, but I am stuck.
ChartMaster