Active Selection turned into Range

[817279]

Hello all,

I have come across this problem multiple times and finally decided to receive help and solve this problem. In VBA I utilize Range(Selection, Selection.End(xlToLeft)).Select often in order to select a large range of data with a variable amount of rows or columns. This works well until I want to plug that active selection into a tool in Excel like Sort and Filtering.

The below code and attached workbook is an example of this issue. This is a schedule spreadsheet that I would like to give to my superintendent. I would like him to be able to add rows to the schedule and then be able to press a button and sort it by beginning date but because the number of rows can change problems occur like filtering the wrong part of the sheet
If there was a way to put the active selection into the range this would solve the problem or is there a way to write code to make VBA extract the active selection and output it as a range or something

Thank you very much for any help,
Andy

'Sorting Code
         ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add Key:=Range("AI10:AI38"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
    'I used record macro to figure out how to make vba sort but it wont let the range be the selected area
    'This will be a problem when the user adds or removes rows so I need vba to sort values in a range given
    ' that the number of rows could change
        With ActiveWorkbook.Worksheets("Schedule").Sort
            .SetRange Range("A10:AI38")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

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
    'I used record macro to figure out how to make vba sort but it wont let the range be the selected area
    'This will be a problem when the user adds or removes rows so I need vba to sort values in a range given
    ' that the number of rows could change
        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






'After you run this code you will need to replace the X's with the
'disordered ones if you would like to experiment more.
'Copy and paste the second sheet to do this






    Columns("AI:AI").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select
End Sub

[Norie]

Perhaps you could use Selection.CurrentRegion which should return the 'region' the selected cell(s) are in.

[817279]

Selection.CurrentRegion still gives an error

See attached Picture for more detail

[Norie]

How exactly did you try using Selection.CurrentRegion?

[817279]

    'Sorting Code
            ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Clear
            ActiveWorkbook.Worksheets("Schedule").Sort.SortFields.Add Key:=Selection.CurrentRegion, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
               xlSortNormal
            With ActiveWorkbook.Worksheets("Schedule").Sort
                .SetRange Selection.CurrentRegion
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With