Rob Mccormack
New member
- Joined
- Nov 18, 2013
- Messages
- 3
- Reaction score
- 0
- Points
- 0
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.
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.
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.
Attachments
Last edited by a moderator: