Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different way to sort?
I thought I had everything working properly in this weekends project. Tonight,
I imported the weekly csv, and now I have another problem: When I'm finished with everything, the last step is to sort it all based on 3 columns. I recorded a macro to do that, shown below. I see the problem now is when I record it, it throws in values like "Y11290". I don't want it to be Y11290, because if next weeks file is 15,000 lines long, it won't all get sorted. How do I change this so that it always sorts on all of the data? Thanks in advance. Columns("A:AD").Select Range("AD1").Activate ActiveWorkbook.Worksheets("import").Sort.SortField s.Clear ActiveWorkbook.Worksheets("import").Sort.SortField s.Add Key:=Range( _ "AD2:AD11290"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("import").Sort.SortField s.Add Key:=Range( _ "F2:F11290"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("import").Sort.SortField s.Add Key:=Range( _ "Y2:Y11290"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("import").Sort .SetRange Range("A1:AD9325") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Different way to sort?
Untested
Dim LastRow As Long Columns("A:AD").Select Range("AD1").Activate LastRow = Cells(Rows.Count, "AD").End(xlUp).Row With ActiveWorkbook.Worksheets("import") .Sort.SortFields.Clear .Sort.SortFields.Add _ Key:=Range("AD2").Resize(LastRow - 1), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal .Sort.SortFields.Add _ Key:=Range("F2").Resize(LastRow - 1), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal .Sort.SortFields.Add _ Key:=Range("Y2").Resize(LastRow - 1), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal With .Sort .SetRange Range("A1").Resize(LastRow - 1) .Header = xlYes .SortMethod = xlPinYin .Apply End With End With -- __________________________________ HTH Bob <Melvin Purvis wrote in message ... I thought I had everything working properly in this weekends project. Tonight, I imported the weekly csv, and now I have another problem: When I'm finished with everything, the last step is to sort it all based on 3 columns. I recorded a macro to do that, shown below. I see the problem now is when I record it, it throws in values like "Y11290". I don't want it to be Y11290, because if next weeks file is 15,000 lines long, it won't all get sorted. How do I change this so that it always sorts on all of the data? Thanks in advance. Columns("A:AD").Select Range("AD1").Activate ActiveWorkbook.Worksheets("import").Sort.SortField s.Clear ActiveWorkbook.Worksheets("import").Sort.SortField s.Add Key:=Range( _ "AD2:AD11290"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("import").Sort.SortField s.Add Key:=Range( _ "F2:F11290"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal ActiveWorkbook.Worksheets("import").Sort.SortField s.Add Key:=Range( _ "Y2:Y11290"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("import").Sort .SetRange Range("A1:AD9325") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating workbook with an alpha sort sheet and a numeric sort shee | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
Pls. reply Sort Data and copy to next coulmn when sort order chang | Excel Programming | |||
Excel sort by Fill Color by custom list sort | Excel Discussion (Misc queries) | |||
sort (on part of) string - originally posted under Tricky Sort | Excel Programming |