Hi,

Am Tue, 8 Jul 2014 11:08:29 -0700 (PDT) schrieb krio_gyal:

The problem is where it says " Range("B7:N21").Select" and "Range("B23:N41").Select". With the first sort, the "Total" row ended up in row 22. Now I need the next sort to first sort everything above the Total row and then everything below it, keeping in mind, that depending on the data which changes weekly, the Total row could end up being any row after the first sort. Is there a way to do this?

write the "Total" row in your code.

Sub Sort()
Dim TotalR As Long
Dim LRow As Long

'Modify TotalRow to suit
TotalR = 36

With Sheets("Over_Under_Dept")
LRow = .Cells(Rows.Count, 2).End(xlUp).Row

.Range("B7:N" & TotalR - 1).Sort key1:=.Range("E7"), _
order1:=xlDescending, Header:=xlYes

'if you have no total row beneath this range
'delete the -1 behind LRow
.Range(.Cells(TotalR + 1, "B"), .Cells(LRow - 1, "N")).Sort _
key1:=.Cells(TotalR + 1, 5), order1:=xlDescending, Header:=xlNo
End With

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional