troubleshooting Question

How to sort sections on Excel worksheet with VBA

Avatar of dkcoop03
dkcoop03Flag for United States of America asked on
Microsoft Excel
17 Comments1 Solution448 ViewsLast Modified:
I have a worksheet (attached) that has three sections.  I want to sort the first section by the last column (Sort Column) Column G.  So I will sort A2:G17 using column G.  Then I want the next two sections to be in the same order as the first section.  The code for sorting the first section is as follows:
Private Sub cmdSort_Click()
Dim r As Range

Dim lastrow As Integer
Dim row As Integer
Dim lrow As Integer
Dim newval As String
Dim sortval As String
Dim tostring As Range


'Hardcode all this stuff for now

'GET LAST ROW FOR COUNTS
lastrow = Range("B2").End(xlDown).row
row = 2
lrow = 2

'Hardcode all this stuff for now
Set r = Range("H2")

Set tostring = Range("I2")

'CLEAR ALL RANGES USED FOR SORT
Range("A2:I17").ClearContents

While r.row <= lastrow
    newval = Format$((r.Offset(0, -2).Value + 2000) * 100, "00000000")
    r.Formula = newval
    sortval = Format$(r.Formula, "00000000")
    tostring.NumberFormat = "@"
    tostring.Formula = sortval

    'drop down one row on all ranges
    Set r = r.Offset(1, 0)
    Set tostring = tostring.Offset(1, 0)
Wend

Call sortall(lastrow)

Range("h2:I17").ClearContents
End Sub
Private Sub sortall(lastrow As Integer)
    Range(("2:" & lastrow)).Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlNo
End Sub

This is code that I used for another sort and I had to do the addition and leading zeros to overcome some negative number problems.  This code works for the first section.  My question is, now how do I sort the 2nd & 3rd sections to be in the order of the first section?  Also, I'm sure there is a better way to sort the first section so any suggestions would be appreciated.

Also, I must do this in VBA since I need the sort to be done on a button click.
Thanks,
testworksheetforsort.xlsx
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 17 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros