Excel VBA - Clarification

Question

I was just wondering if anybody had any advice on what this piece of VBA code is doing. A colleague of mine left recently and hadn't commented any of their work so I am a bit confused.

There is some code to sort some cells but I'm not sure how it knows what to sort, as a range is specified which isn't the data I want sorting. I think I am missing something but nothing online seems to explain Excel VBA in a very good manner.

The code looks like this:

    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.ActiveSheet.SORT.SortFields.Clear
    ActiveWorkbook.ActiveSheet.SORT.SortFields.Add Key:=ActiveCell, _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.ActiveSheet.SORT
        .SetRange ActiveCell.Range("A1:A550")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveCell.Offset(0, 1).Range("A1").Select

I know that the top stuff sets the criteria to be used to sort the data, but I am unsure why range("A1:A550") is there, as the data to be sorted usually begins somewhere around cell C15. Is the code just saying "Use the current cell as A1, select the next 550 rows down from this and sort the data accordingly"?

Thank you all in advance.

'nothing online seems to explain Excel VBA in a very good manner'? You cannot be serious? There are thousands of tutorials out there, and then there is the official documentation at learn.microsoft.com/en-us/office/vba/api/overview/excel — Rno
– Rno, Commented Dec 5, 2018 at 13:54
OK, maybe not so much that it's not in a good manner, but I can't seem to apply the information to the code above. I'm sure it's something simple and something that I am missing that is glaringly obvious, but it's all starting to melt together and look the same — abberto
– abberto, Commented Dec 5, 2018 at 13:55
A1:A550 is relative to the selected cell. If A1 is selected then it will refer to A1:A550. If B2 is selected then that cell is considered to be the A1 of the selection so it will refer to the range B2:B551. The same with ActiveCell.Offset(0, 1).Range("A1") - it's telling it to select the first cell one column across from the ActiveCell - you could just say ActiveCell.Offset(0, 1). — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 5, 2018 at 14:03
Brilliant!! Thank you both! That makes sense and, clearly, I was missing a relatively obvious notion. Thanks again!! — abberto
– abberto, Commented Dec 5, 2018 at 14:06
So all the code is doing is sorting the 550 cells below & including the currently selected cell. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 5, 2018 at 14:07

Chronocidal · Accepted Answer · 2018-12-05 14:21:44Z

1

The .SetRange ActiveCell.Range("A1:A550") line in that code is Relative to the Range determined by the line Range(Selection, Selection.End(xlDown)).Select - and just says to use the first 550 lines. (If there are less than 550 cells selected, it expands the selection to match, so that first line is actually unnecessary)

That said, the entire block can be made much shorter, to do exactly the same thing without any "extraneous" code:

Range(Selection.Cells(1,1), Selection.Cells(550, 1)).Sort Selection, xlAscending, _
    Header:=xlNo, Orientation:=xlSortColumns, SortMethod:=xlPinYin
ActiveCell.Offset(0, 1).Select

edited Dec 5, 2018 at 14:21

answered Dec 5, 2018 at 14:14

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darren Bartrup-Cook Over a year ago

You can remove the SortMethod:=xlPinYin as well unless you're sorting Chinese characters.

Collectives™ on Stack Overflow

Excel VBA - Clarification

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related