range definition by cells numbers

T

tuli

I am trying to sort a few columns. This is the code I have. It does not work

it tells me that the sort reference is not valid

Sub Macro2()
Dim AKO As Range
Dim BKO as Range
Set AKO = Range(Sheet3.Cells(2, 13), Sheet3.Cells(362, 66)) 'Range("M2:M362")
Set BKO = Range(Sheet3.Cells(2, 13), Sheet3.Cells(362, 13)) 'Range("M2:M362")

ActiveWorkbook.Worksheets("RESULTS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("RESULTS").Sort.SortFields.Add Key:=AKO, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("RESULTS").Sort
.SetRange BKO
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub
 
C

Claus Busch

Hi,

Am Fri, 19 Oct 2012 13:35:45 -0700 (PDT) schrieb tuli:
Dim AKO As Range
Dim BKO as Range
Set AKO = Range(Sheet3.Cells(2, 13), Sheet3.Cells(362, 66)) 'Range("M2:M362")
Set BKO = Range(Sheet3.Cells(2, 13), Sheet3.Cells(362, 13)) 'Range("M2:M362")

try:
Set AKO = Sheets("Sheet3").Range(Cells(2, 13), Cells(362, 13))
or:
Set AKO = Sheets("Sheet3").Range("M2:M362")

change your code:

Dim AKO As Range
Dim BKO As Range

With Worksheets("RESULTS")
Set AKO = .Range(Cells(2, 13), Cells(362, 13))
Set BKO = Range(Cells(1, 1), Cells(362, 13))

.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=AKO _
, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.SetRange BKO
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With


Regards
Claus Busch
 
C

Claus Busch

Hi,

Am Fri, 19 Oct 2012 23:05:31 +0200 schrieb Claus Busch:
Set AKO = .Range(Cells(2, 13), Cells(362, 13))
Set BKO = Range(Cells(1, 1), Cells(362, 13))

look for the 2 rows above and change them to:

Set AKO = .Range(.Cells(2, 13), .Cells(362, 13))
Set BKO = .Range(.Cells(1, 1), .Cells(362, 13))


Regards
Claus Busch
 
T

tuli

I am trying to sort a few columns. This is the code I have. It does not work it tells me that the sort reference is not valid Sub Macro2() Dim AKO As Range Dim BKO as Range Set AKO = Range(Sheet3.Cells(2, 13), Sheet3.Cells(362, 66)) 'Range("M2:M362") Set BKO = Range(Sheet3.Cells(2, 13), Sheet3..Cells(362, 13)) 'Range("M2:M362") ActiveWorkbook.Worksheets("RESULTS").Sort.SortFields.Clear ActiveWorkbook.Worksheets("RESULTS").Sort.SortFields.AddKey:=AKO, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("RESULTS").Sort .SetRange BKO .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub

Thanks this worked
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify Sort Routine to inlcude All Data 2
Undo Macro Action 3
Sorting Question 5
Pictures not being sorted in VBA 2
VBA 2 Codes 2
Clear Check Box 2
Stay on Active Sheet 2
VB Macro question 2

Top