Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: Fastest Way to Sort Rows

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    Last edited by shades; 10-21-05 at 11:55.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Posts
    9
    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.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    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.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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