Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplfying the Sort macro
I would like to have a macro to sort every column in descending order.
Given below is the the sort macro I have for each and every column. This implies I have to copy,paste and change the corresponding column name 256 times. Is there any simple way to do this? Thanks Columns("A:A").Select Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("C:C").Select Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("D:D").Select Selection.Sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal etc till Column IV |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplfying the Sort macro
Try
Sub SortEachColumn() Dim LastCol As Long Dim LastRow As Long Dim N As Long LastCol = Cells(1, Columns.Count).End(xlToLeft).Column For N = 1 To LastCol LastRow = Cells(Rows.Count, N).End(xlUp).Row Range(Cells(1, N), Cells(LastRow, N)).Sort _ key1:=Cells(1, N), order1:=xlAscending Next N End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) wrote in message ps.com... I would like to have a macro to sort every column in descending order. Given below is the the sort macro I have for each and every column. This implies I have to copy,paste and change the corresponding column name 256 times. Is there any simple way to do this? Thanks Columns("A:A").Select Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("C:C").Select Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("D:D").Select Selection.Sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal etc till Column IV |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplfying the Sort macro
On Oct 22, 5:10 pm, "Chip Pearson" wrote:
Try Sub SortEachColumn() Dim LastCol As Long Dim LastRow As Long Dim N As Long LastCol = Cells(1, Columns.Count).End(xlToLeft).Column For N = 1 To LastCol LastRow = Cells(Rows.Count, N).End(xlUp).Row Range(Cells(1, N), Cells(LastRow, N)).Sort _ key1:=Cells(1, N), order1:=xlAscending Next N End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consultingwww.cpearson.com (email on the web site) wrote in message ps.com... I would like to have a macro to sort every column in descending order. Given below is the the sort macro I have for each and every column. This implies I have to copy,paste and change the corresponding column name 256 times. Is there any simple way to do this? Thanks Columns("A:A").Select Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("C:C").Select Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("D:D").Select Selection.Sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal etc till Column IV- Hide quoted text - - Show quoted text - Chip, your code works great. Thank you very much. Here is the code I am using to suit my need Sub sort_each_column() Range("A1:IV65536").Value = "" Sheets("Sheet1").Range("A1:IV65536").Copy Sheets("Sheet4").Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Dim LastCol As Long Dim LastRow As Long Dim N As Long LastCol = Cells(1, Columns.Count).End(xlToLeft).Column For N = 1 To LastCol LastRow = Cells(Rows.Count, N).End(xlUp).Row Range(Cells(1, N), Cells(LastRow, N)).Sort _ key1:=Cells(1, N), order1:=xlDescending Next N Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplfying DIV/0 Error...Can you? | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Using Macro to sort without clicking on macro button | Excel Discussion (Misc queries) | |||
Sort Macro | Excel Programming | |||
Sort Macro | Excel Discussion (Misc queries) |