Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I recorded the macro below and need to modify it for use in varing number of records.
================================================== =========== Range("A1:X1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Clear ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Add Key:=Range("A2:A7746" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Add Key:=Range("F2:F7746" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Add Key:=Range("I2:I7746" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("sectxW").SORT .SetRange Range("A1:X7746") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ================================================== ======================== I guess the following need to be modified to accept variable number of records :Range("A2:A7746"), Range("F2:F7746"),Range("I2:I7746"), Range("A1:X7746") Thanks |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi,
Am Mon, 11 Aug 2014 07:51:13 -0700 (PDT) schrieb sylink: I guess the following need to be modified to accept variable number of records :Range("A2:A7746"), Range("F2:F7746"),Range("I2:I7746"), Range("A1:X7746") is that what you need? Sub Test() Dim LRow As Long, LCol As Long, i As Long Dim varKey As Variant Application.ScreenUpdating = False With Sheets("sectxW") 'Last row LRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Last column LCol = .Cells(1, Columns.Count).End(xlUp).Column 'Sortkeys varKey = Array("A1", "F1", "I1") .Sort.SortFields.Clear For i = LBound(varKey) To UBound(varKey) .Range(.Cells(1, 1), .Cells(LRow, LCol)).Sort _ Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlYes Next End With Application.ScreenUpdating = True End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Monday, August 11, 2014 3:51:13 PM UTC+1, sylink wrote:
I recorded the macro below and need to modify it for use in varing number of records. ================================================== =========== Range("A1:X1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Clear ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Add Key:=Range("A2:A7746" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Add Key:=Range("F2:F7746" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("sectxW").SORT.SortField s.Add Key:=Range("I2:I7746" _ ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("sectxW").SORT .SetRange Range("A1:X7746") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ================================================== ======================== I guess the following need to be modified to accept variable number of records :Range("A2:A7746"), Range("F2:F7746"),Range("I2:I7746"), Range("A1:X7746") Thanks Many thanks. I will try it without delay. |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
On Monday, August 11, 2014 4:08:45 PM UTC+1, Claus Busch wrote:
Hi, Am Mon, 11 Aug 2014 07:51:13 -0700 (PDT) schrieb sylink: I guess the following need to be modified to accept variable number of records :Range("A2:A7746"), Range("F2:F7746"),Range("I2:I7746"), Range("A1:X7746") is that what you need? Sub Test() Dim LRow As Long, LCol As Long, i As Long Dim varKey As Variant Application.ScreenUpdating = False With Sheets("sectxW") 'Last row LRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Last column LCol = .Cells(1, Columns.Count).End(xlUp).Column 'Sortkeys varKey = Array("A1", "F1", "I1") .Sort.SortFields.Clear For i = LBound(varKey) To UBound(varKey) .Range(.Cells(1, 1), .Cells(LRow, LCol)).Sort _ Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlYes Next End With Application.ScreenUpdating = True End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi I have really tested the script and found out that it needs a little adjustment to meet my need. Instead of sorting freshly thru each iteration, what I actually need is for the script to: replicate customer sort with 3 levels each depending on previous execution. e.g Level1 -sort by Level2 -then by Level3 - Then by Exampl: to sort alphabetical/ascending for 2 levels only: ref Amt == ==== c 2 c 1 a 6 a 5 Correct Result ref Amt === ===== a 5 a 6 c 1 c 2 |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Hi,
Am Thu, 21 Aug 2014 09:10:58 -0700 (PDT) schrieb sylink: I have really tested the script and found out that it needs a little adjustment to meet my need. Instead of sorting freshly thru each iteration, what I actually need is for the script to: then try: Sub SortTest() Dim LRow As Long Dim Lcol As Long Dim i As Long Dim varKey As Variant Application.ScreenUpdating = False varKey = Array("A1", "F1", "I1") With Sheets("sectxW") Lcol = .Cells(1, Columns.Count).End(xlUp).Column LRow = .Cells(Rows.Count, 1).End(xlUp).Row .Sort.SortFields.Clear For i = LBound(varKey) To UBound(varKey) .Sort.SortFields.Add Key:=.Range(varKey(i)) _ , SortOn:=xlSortOnValues, Order:=xlAscending Next With .Sort .SetRange Range(Cells(1, 1), Cells(LRow, Lcol)) .Header = xlYes .MatchCase = False .Apply End With End With Application.ScreenUpdating = True End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I sort variables into sets of three? | Excel Discussion (Misc queries) | |||
Sort with two variables | New Users to Excel | |||
Sort by Variables | Excel Programming | |||
Linking Cells and applying a sort | Excel Worksheet Functions | |||
sort for variables | Excel Programming |