Picking Rows based on initially selected cells and using VBA

Axel_Lerner

New member
Joined
Mar 22, 2015
Messages
4
Reaction score
0
Points
0
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
 
First row

Code:
Rows(Target.Row))

Last row

Code:
Rows(Target.Rows.Count + Target.Row - 1),1)
 
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[COLOR=#008000]  'supposed to sound like Start of Row without invoking a keyword[/COLOR]
Dim Ehnd As Integer   [COLOR=#008000]'supposed to sound like End of Row without invoking a keyword[/COLOR]

[B]Staht = Rows(Target.Row) [/B][COLOR=#008000]'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.[/COLOR]
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     [COLOR=#008000]'intent was to sort by one value[/COLOR]
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
        "AP" & Staht & ":AP" & Ehnd), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal    [COLOR=#008000]'my intent was to ALSO sort by another value in case the first value was the same[/COLOR]
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("AD" & Staht & ":BP" & Ehnd)    [COLOR=#008000]'this part selects the Old info[/COLOR]
        .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 a moderator:
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
 
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:
Back
Top