Results 1 to 7 of 7

VBA range and selection

Threaded View

  1. #1
    Registered User
    Join Date
    06-26-2017
    Location
    Colorado
    MS-Off Ver
    2017 and 2013
    Posts
    7

    VBA range and selection

    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
    Last edited by 817279; 06-26-2017 at 06:25 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error 1004 Method range of object when trying to clear range selection
    By shoulddt in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2017, 10:09 PM
  2. Copy specific range selection to msword but why it copies columns not in range?
    By adbasanta in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-11-2016, 03:46 AM
  3. Replies: 0
    Last Post: 12-10-2014, 11:30 AM
  4. [SOLVED] I need help range selection and cells selection
    By venkatpvc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2013, 07:36 PM
  5. [SOLVED] Range(Selection, Selection.End(xlDown)).Select goes to 65536 instead of last filled row
    By looney in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-12-2013, 10:51 AM
  6. Replies: 2
    Last Post: 04-20-2012, 12:23 PM
  7. Hide rows in range 2 based on filter selection in range 1
    By gtol in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-28-2012, 02:09 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1