Results 1 to 6 of 6

Thread: Picking Rows based on initially selected cells and using VBA

  1. #1

    Picking Rows based on initially selected cells and using VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm using MS Excel for Mac 2011.

    I want to pick a contiguous group of cells in my Excel spreadsheet, then run a macro. Note that the number of rows picked could be highly variable, and the first row picked could be highly variable.

    I want that macro to know which rows I've picked, and sort a DIFFERENT group of contiguous cells in the same exact rows as I picked before running the macro. And then sort again on yet another different group of contiguous cells in the same exact rows as I picked before running the macro. In both "sorts" I plan to hard code in the columns for sorting.

    I don't understand Excel VBA "grammar" at all. I learned BASIC a long time ago, but VBA is different, and I forgot a lot of BASIC anyhow.

    I've tried things for a couple days and gotten nowhere. I'm stuck on how to transfer the starting row and final row into VBA. The sorting code is not the problem, since I can just "Record Macro" and get the commands for that.

    I have tried Range("D"&(ActiveCell.Row,1).Select
    That gets me to the current row at least, but ActiveCell.RowHeight doesn't seem to get me to the last row. If Selection.Offset is the answer, I'm not doing it right.

    (My first post. Assume I know none of the lingo! For instance: I tried googling for answers but I must be using the words "selection" and "range" incorrectly.)

    Axel

  2. #2
    First row

    Code:
    Rows(Target.Row))
    Last row

    Code:
    Rows(Target.Rows.Count + Target.Row - 1),1)

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    First row

    Code:
    Rows(Target.Row))
    Last row

    Code:
    Rows(Target.Rows.Count + Target.Row - 1),1)

    Thank you Bob.

    It wouldn't quite let me type those exact words but here's what I came up with.

    -----

    Code:
    Sub Cosort()
    
    Dim Staht As Integer  'supposed to sound like Start of Row without invoking a keyword
    Dim Ehnd As Integer   'supposed to sound like End of Row without invoking a keyword
    
    Staht = Rows(Target.Row) 'this gives me a "Runtime error '424' Object Required" error.  My intent was for Staht to act as a variable that the rest of the VBA program could use.  Like "Let X = 5" in BASIC.
    Ehnd = Rows(Target.Rows.Count + Target.Row - 1)
    
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
            "AL" & Staht & ":AL" & Ehnd), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal     'intent was to sort by one value
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
            "AP" & Staht & ":AP" & Ehnd), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal    'my intent was to ALSO sort by another value in case the first value was the same
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("AD" & Staht & ":BP" & Ehnd)    'this part selects the Old info
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    
    
    End Sub

    -----

    I tried Step Into, and it failed when I got to the BOLD line, which gives me a "Runtime error '424' Object Required" error. My intent was for Staht to act as a variable that the rest of the VBA program could use. Like "Let X = 5" in BASIC.

    Axel


    P.S. The contents of the cells are not important.

    If I pick D23:E26, then run the macro, I want the computer to sort on F23:H26, then sort on A23:A26, then sort on A23:H26.

    Then in my original spreadsheet I might next pick F34:G256, then run the macro, and I want the computer to sort on F34:H256, then sort on A34:A256, then sort on A34:H256.

    And so on.
    Last edited by Bob Phillips; 2015-03-23 at 02:36 PM. Reason: Added code tags

  4. #4
    I had assumed that you were using a worksheet event, and you wanted a range object.

    Try this

    Code:
    Sub Cosort()Dim StartRow As Long
    Dim EndRow As Long
    Dim Target As Range
    Dim SortRange As Range
    
    
        Set Target = Selection
        StartRow = Target.Row
        EndRow = Target.Rows.Count + Target.Row - 1
    
    
        With ActiveWorkbook.Worksheets("Sheet1")
        
            Set SortRange = .Range(.Cells(StartRow, "AL"), .Cells(EndRow, "BP"))
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=SortRange.Cells(1, 1), _
                                 SortOn:=xlSortOnValues, _
                                 Order:=xlAscending, _
                                 DataOption:=xlSortNormal              'intent was to sort by one value
            .Sort.SortFields.Add Key:=SortRange.Cells(1, 5), _
                                 SortOn:=xlSortOnValues, _
                                 Order:=xlAscending, _
                                 DataOption:=xlSortNormal             'my intent was to ALSO sort by another value in case the first value was the same
            With .Sort
                .SetRange SortRange
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End Sub

  5. #5
    Thank you for your advice. My macro (with your help!) is working much better but I find I still have a peculiar snag. The first category I try to sort on does not usually sort! The second and third categories sort just fine. I tried altering the order within the macro. I tried reducing the width of the range (my spreadsheet is fairly wide). I checked that the wording in the macro for each was the same.

    What causes that?

    Code:
    Sub SrtOldNewSngs()
    '
    ' Sort Old then New Songs Macro
    '
    
    
    Dim StartRow As Long
    Dim EndRow As Long
    Dim Target As Range
    Dim SortRange As Range
    
    
        Set Target = Selection
        StartRow = Target.Row
        EndRow = Target.Rows.Count + Target.Row - 1
    
    
    'Sort Old Songs
        With ActiveWorkbook.Worksheets("Sheet1")
        
            Set SortRange = .Range(.Cells(StartRow, "AD"), .Cells(EndRow, "BX"))
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=SortRange.Cells(1, "AL"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal    'intent was to sort on the old name column AL
            .Sort.SortFields.Add Key:=SortRange.Cells(1, "AP"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal    'intent was to sort on the old real date added column
            With .Sort
                .SetRange SortRange
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
        End With
    End With
        
    'Sort New Songs
        With ActiveWorkbook.Worksheets("Sheet1")
        
            Set SortRange = .Range(.Cells(StartRow, "A"), .Cells(EndRow, "AC"))
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=SortRange.Cells(1, "A"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal    'intent was to sort on the name column, i.e. A
            .Sort.SortFields.Add Key:=SortRange.Cells(1, "O"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal    'intent was to sort on the new date added column, i.e. O
            With .Sort
                .SetRange SortRange
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
        End With
    End With
    
    
    'Sort All Songs
        With ActiveWorkbook.Worksheets("Sheet1")
        
            Set SortRange = .Range(.Cells(StartRow, "A"), .Cells(EndRow, "BX"))
            .Sort.SortFields.Clear
            .Sort.SortFields.Add Key:=SortRange.Cells(1, "AC"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal      'intent was to sort on the Sequence column
            With .Sort
                .SetRange SortRange
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End Sub
    I find that the "sort old songs" portion does NOT usually sort the range AD to BX on AL. It's mystifying.

    I can leave the macro entirely and sort on it just fine using the DATA --> SORT command. I can record a macro of sorting it, and it sorts just fine. But when I modify the recorded macro to allow the flexible ranges discussed in the original post, it stops sorting on AL.
    Last edited by Axel_Lerner; 2015-04-10 at 06:03 AM. Reason: fixed code box

  6. #6
    thanks buddy it was help full

Tags for this Thread

Posting Permissions

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