writing a sort macro

  • Thread starter Thread starter krio_gyal
  • Start date Start date
K

krio_gyal

Hi, I'm relatively inexperienced when it comes to macros. I usually record a macro and then edit to do what i want, but I'm having trouble with this one. Here's what i recorded:

ub Sort_OverUnder()
'
' Sort_OverUnder Macro
' Sort lines for Over/Under Report
'
' Keyboard Shortcut: Ctrl+q
'
Range("B6:N41").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Add Key:=Range( _
"F7:F41"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B6:N41")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B7:N21").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Add Key:=Range( _
"E7:E21"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B6:N21")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B23:N41").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Add Key:=Range( _
"E23:E41"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B23:N41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("I22").Select
End Sub

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 changesweekly, the Total row could end up being any row after the first sort. Is there a way to do this?

Thanks.
 
Am Tue, 8 Jul 2014 11:08:29 -0700 (PDT) schrieb krio_gyal:
Hi, I'm relatively inexperienced when it comes to macros. I usually record a macro and then edit to do what i want, but I'm having trouble with this one. Here's what i recorded:

ub Sort_OverUnder()
'
' Sort_OverUnder Macro
' Sort lines for Over/Under Report
'
' Keyboard Shortcut: Ctrl+q
'
Range("B6:N41").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Add Key:=Range( _
"F7:F41"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B6:N41")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B7:N21").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Add Key:=Range( _
"E7:E21"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B6:N21")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B23:N41").Select
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Over_Under_Dept").Sort.SortFields.Add Key:=Range( _
"E23:E41"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Over_Under_Dept").Sort
.SetRange Range("B23:N41")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("I22").Select
End Sub

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?

Thanks.
Click to expand...


Regards
Claus B.
 
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?
Click to expand...

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.
 
Back
Top