Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
writing a sort macro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
writing a sort macro
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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
writing a sort macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro writing | Excel Worksheet Functions | |||
Writing a Sort Macro | Excel Programming | |||
Writing a macro to sort and export data to multiple worksheets | Excel Programming | |||
writing macro | New Users to Excel | |||
Writing 2 macros to sort names and numbers in excel 97 | Excel Programming |