writing a sort macro

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.
 
C

Claus Busch

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.


Regards
Claus B.
 
C

Claus Busch

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Variables to SORT 4
VB Macro question 2
Automatic sort macro 2
Sort by column, not range 1
Undo Macro Action 3
Macro Help 3
range definition by cells numbers 3
VBA 2 Codes 2

Top