Shortening code

[Rob Mccormack]

Hi, i dont have much knowledge about excel VBA as i am just starting to use it.

Anyway, the document i am using has a list of vehicles used by companies with the company name in the A column, the number plate in the B colomn and then other less relevent information across the row. What i want is the rows to be in alphabetic order by company name (the A column). Once they are in order by company name, i then want them to be in alphabetic order by number plate within the company (the B column).

I'm not sure if i have explained myself very well so I have attached a "before" and "after" printscreen of what should happen.

I have currently got some code that does this that was taken from a macro i recorded. This is pasted below.

Sheets("AB Vehicles").Select
ActiveWindow.SmallScroll Down:=-96
Range("A2:BC1000").Select
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-105
ActiveWorkbook.Worksheets("AB Vehicles").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("AB Vehicles").Sort.SortFields.Add Key:=Range("A2") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("AB Vehicles").Sort
.SetRange Range("A2:BC1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

This code is far too long and i could really do with it being a lot smaller. I have searched in google for an answer but the result i got, put the two columns in order but didnt keep the row information together so all my information got messed up. Can anyone help me shorten it?

Thanks.

[Ken Puls]

Hi Rob,

While I wouldn't necessarily be too concerned about code length for the sake of only that, you certainly have a lot of lines that can be cleaned up. Unless you like watching the mouse scroll we can wipe all the ActiveWindow.ScrollColumn lines, as that's all they do.

I've cleaned up your code for you, and added the second sortfield I think you're looking for. Give this a go and let me know if it works for you:

Sub SortMe()
    With Worksheets("AB Vehicles")
    
        .Range("A2:BC1000").Select
    
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending
        .Sort.SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending
        
        With ActiveWorkbook.Worksheets("AB Vehicles").Sort
            .SetRange Range("A2:BC1000")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

[snb]

or ?

Sub M_snb()
    with activeworkbook.Worksheets("AB Vehicles")
       .Range("A2:BC1000").Sort .Range("A2"), ,.Range("B2"), , , , ,xlno
    end with
End Sub

[Rob Mccormack]

Thanks guys, they both work!

I am following a booklet on how to start programming in Excel but the version in this booklet is an older version than mine and has the "File, Edit, View, Insert... etc" menu. How do you get that menu to appear on newer versions of Excel?

[snb]

Buy another book: VBA for Dummies (J. Walkenbach)

Excel 95 has long gone....