range definition by cells numbers

  • Thread starter Thread starter tuli
  • Start date Start date
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
 
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")
Click to expand...

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

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

Thanks this worked
 
Back
Top