|
Sort Macro
Hi All
Below is the code for a sort Macro works OK no problem - the question is that on the sheet there are 20 blocks of 4 line ranges all in the same columns. How would I incorporate them in this little macro. Ranges are as follows C2:D5, C6:D9, C10:D13, C14:D17 as so on down to ranges C54:D57 Sub Macro8() Sheets("Lindop").Select Range("C2:D5").Select ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Add Key:=Range("D22:D25") _ , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lindop").Sort .SetRange Range("C2:D5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Many thanks hazel |
Sort Macro
hi Hazel,
I copied your code and pasted into a standard module and my compiler freaked out. turned all the code red. so i rewrote it. try this. Sub Sort4LinesInColumn() Sheets("Sheet2").Select Range("C2").Select Do While Not IsEmpty(ActiveCell) Range(Selection, Selection.Offset(3, 1)).Select Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveCell.Select ActiveCell.Offset(4, 0).Activate Loop End Sub I am using xp and this code sorted 4 groups of 4 lines in the same columns ...number and letters. give it a try and post back if you have problems. do it in step mode. Regards FSt1 "Hazel" wrote: Hi All Below is the code for a sort Macro works OK no problem - the question is that on the sheet there are 20 blocks of 4 line ranges all in the same columns. How would I incorporate them in this little macro. Ranges are as follows C2:D5, C6:D9, C10:D13, C14:D17 as so on down to ranges C54:D57 Sub Macro8() Sheets("Lindop").Select Range("C2:D5").Select ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Add Key:=Range("D22:D25") _ , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lindop").Sort .SetRange Range("C2:D5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Many thanks hazel |
Sort Macro
Hi
Going to have a go now -- probably should have indicated I recorded the macro in Excel 2007. Will let you know how I get on. -- Many thanks hazel "FSt1" wrote: hi Hazel, I copied your code and pasted into a standard module and my compiler freaked out. turned all the code red. so i rewrote it. try this. Sub Sort4LinesInColumn() Sheets("Sheet2").Select Range("C2").Select Do While Not IsEmpty(ActiveCell) Range(Selection, Selection.Offset(3, 1)).Select Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveCell.Select ActiveCell.Offset(4, 0).Activate Loop End Sub I am using xp and this code sorted 4 groups of 4 lines in the same columns ..number and letters. give it a try and post back if you have problems. do it in step mode. Regards FSt1 "Hazel" wrote: Hi All Below is the code for a sort Macro works OK no problem - the question is that on the sheet there are 20 blocks of 4 line ranges all in the same columns. How would I incorporate them in this little macro. Ranges are as follows C2:D5, C6:D9, C10:D13, C14:D17 as so on down to ranges C54:D57 Sub Macro8() Sheets("Lindop").Select Range("C2:D5").Select ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Add Key:=Range("D22:D25") _ , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lindop").Sort .SetRange Range("C2:D5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Many thanks hazel |
Sort Macro
Hi
I have inserted and run your code - however it will not sort the criteria of column D followed by column C -- again I have looked at the macro above and there is an error in the typing that is probably why the code turned Red when you tried have highlighted the error below. Perhaps another go at it please. Sub Macro8() Sheets("Lindop").Select Range("C2:D5").Select ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Add 'Key:=Range("D2:D5") _ <<< should be this not the line below Key:=Range("D22:D25") _ , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lindop").Sort ..SetRange Range("C2:D5") ..Header = xlGuess ..MatchCase = False ..Orientation = xlTopToBottom ..SortMethod = xlPinYin ..Apply End With End Sub -- Many thanks hazel "FSt1" wrote: hi Hazel, I copied your code and pasted into a standard module and my compiler freaked out. turned all the code red. so i rewrote it. try this. Sub Sort4LinesInColumn() Sheets("Sheet2").Select Range("C2").Select Do While Not IsEmpty(ActiveCell) Range(Selection, Selection.Offset(3, 1)).Select Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveCell.Select ActiveCell.Offset(4, 0).Activate Loop End Sub I am using xp and this code sorted 4 groups of 4 lines in the same columns ..number and letters. give it a try and post back if you have problems. do it in step mode. Regards FSt1 "Hazel" wrote: Hi All Below is the code for a sort Macro works OK no problem - the question is that on the sheet there are 20 blocks of 4 line ranges all in the same columns. How would I incorporate them in this little macro. Ranges are as follows C2:D5, C6:D9, C10:D13, C14:D17 as so on down to ranges C54:D57 Sub Macro8() Sheets("Lindop").Select Range("C2:D5").Select ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Clear ActiveWorkbook.Worksheets("Lindop").Sort.SortField s.Add Key:=Range("D22:D25") _ , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Lindop").Sort .SetRange Range("C2:D5") .Header = xlGuess .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Many thanks hazel |
| All times are GMT +1. The time now is 07:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com