Using the Sort ability of Excel I would like to plug in the Active Cells into the Range rather than a hardcoded number or cell
So instead of
ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add Key:=Range("AI10:AI38"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Schedule").Sort
.SetRange Range("A10:AI38")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
I would like the ranges to be the active selection
Like ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add Key:=Range("SELECTION"), SortOn:=xlSortOnValues, Order:=xlAscending,
If this is unclear then you can look at all my code below (Yes Segment 1 works but I needs to be rewritten also to account for added rows)
Sub Sort()
'This Sub sorts the values based on their date
'Make sure the Sub runs on the correct sheet
Sheets("Schedule").Select
'Unhide the Match Equation Column
Columns("AH:AJ").Select
Selection.EntireColumn.Hidden = False
'Seg 1 will always start on cell AI10
Range("AI10").Select
'Selects the entire range
For Ctr = 1 To 23 'Set up to counteract the worst case of work EVERY OTHER DAY
Range(Selection, Selection.End(xlToLeft)).Select
Next
Range(Selection, Selection.End(xlDown)).Select
'Sorting Code
ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add Key:=Range( _
"AI10:AI38"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Schedule").Sort
.SetRange Range("A10:AI38")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'************************************************************************************
' Now to Seg 2
'************************************************************************************
'Find Seg 2 Civil
'Initialize Column Counter
Rctr = 10
'Find Row
Do Until Cells(Rctr, 1) = "SEG 2 CIVIL"
Rctr = Rctr + 1
Loop
'Select the Ref Cell
Cells(Rctr + 1, 35).Select
'REPEAT CODE FROM SEG 1
'Selects the entire range
For Ctr = 1 To 23 'Set up to counteract the worst case of work EVERY OTHER DAY
Range(Selection, Selection.End(xlToLeft)).Select
Next
Range(Selection, Selection.End(xlDown)).Select
'Sorting Code
ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add Key:=Selection, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Schedule").Sort
.SetRange Selection
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("AI:AI").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
End Sub
Bookmarks