Applying Variables to SORT

  • Thread starter Thread starter sylink
  • Start date Start date
S

sylink

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.SortFields.Clear
ActiveWorkbook.Worksheets("sectxW").SORT.SortFields.Add Key:=Range("A2:A7746" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("sectxW").SORT.SortFields.Add Key:=Range("F2:F7746" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("sectxW").SORT.SortFields.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
 
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")
Click to expand...

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.
 
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.SortFields.Clear

ActiveWorkbook.Worksheets("sectxW").SORT.SortFields.Add Key:=Range("A2:A7746" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("sectxW").SORT.SortFields.Add Key:=Range("F2:F7746" _

), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveWorkbook.Worksheets("sectxW").SORT.SortFields.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
Click to expand...

Many thanks. I will try it without delay.
 
Hi,



Am Mon, 11 Aug 2014 07:51:13 -0700 (PDT) schrieb sylink:






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
Click to expand...


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
 
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:
Click to expand...

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.
 
Back
Top