Fastest Way to Sort Rows

mBlack · #1 (**permalink**) 10-21-05, 09:00

Can anyone share a fast way to sort rows (ascending or descending) in each section of a worksheet based on the value of a specified column using VBA?

I have working code but there are many lines and seems very cumbersome. I know there is a quicker way to do it that I am not aware of.

Attached is a workbook with an example of what I am trying to accomplish. I need to sort the rows in each section by Flight/Truck number or by the ETA/ETD.

Any help would greatly be appreciated!

Mike

shades · #2 (**permalink**) 10-21-05, 10:52

Howdy.

Here is code that will do this.

Code:

Sub test()
Application.ScreenUpdating = False
    Range("A5:F14").Sort Key1:=Range("A4"), Order1:=xlAscending
    Range("A19:F28").Sort Key1:=Range("A18"), Order1:=xlAscending
    Range("A33:F42").Sort Key1:=Range("A32"), Order1:=xlAscending
    Range("A47:F56").Sort Key1:=Range("A46"), Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

However, if you will be having more items in each section, it might be better to have each one on its own worksheets. Or, putting them horizontally so that they can grow vertically. I would suggest using Rows.Count to then determine how many items to sort for each section.

mBlack · #3 (**permalink**) 10-21-05, 11:19

Thanks for the info! I guess I should have given more information. Each section has the potential to grow or shrink based on the day and the number of flights / truck routes. I have code that dynamically re-sizes each section accordingly. This is where the sorting gets a little more complicated since the ranges are dynamic.

shades · #4 (**permalink**) 10-21-05, 12:58

I think all the more reason to separate the data into separate columns or even worksheets. Then the dynamic growth is easier to maintain and code. And it should be faster.

Internet Services

Web Development

Digital Marketing

Consumer Tech