Results 1 to 5 of 5

Thread: Shortening code

  1. #1

    Question Shortening code



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

    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.

    Code:
    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.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	before.png 
Views:	24 
Size:	23.5 KB 
ID:	1833   Click image for larger version. 

Name:	after.png 
Views:	12 
Size:	22.7 KB 
ID:	1834  
    Last edited by Ken Puls; 2013-11-18 at 02:37 PM. Reason: Added code tags for display

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,271
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:

    Code:
    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
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    or ?

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

  4. #4
    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?

  5. #5
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    Buy another book: VBA for Dummies (J. Walkenbach)

    Excel 95 has long gone....

Posting Permissions

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