Trying to set up a sort using a defined range

[Downes51]

Hello,

I'm trying to sort some data according to a column selected via a Message Box.

The code I'm using is;

Dim Answer As String
Dim MyNote As String
Dim RowCounter As Integer
Dim MyRange As Range

Range("A1").Select
Selection.End(xlDown).Select
RowCounter = ActiveCell.Row 'this tells me how tall the data column is across all the rows

'ChoiceOne:
MyNote = "The customers will now be sorted according to their TOTAL debt. Answer NO for other options."

Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Sort options")

If Answer = vbNo Then

GoTo ChoiceTwo
Else
Set MyRange = ActiveSheet.Range(Cells(2, 11), Cells(RowCounter, 11))

GoTo Sort
End If

[I'm putting some more dialogue boxes in here which I'm omitting for now]


Sort:

Cells.Select
ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort.SortFields. _ } This is the line where the macro stops.
Add Key:=Range("MyRange"), SortOn:=xlSortOnValues, Order:=xlDescending, _ } I think it's not recognising "MyRange" as a valid parameter
DataOption:=xlSortNormal }
With ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort
.SetRange Range("MyRange")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("A:K").Select 'Fix column widths
Selection.Columns.AutoFit
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With

I'm getting a problem at the time of execution of the Sort instruction. At least I think that's what the Error Message "Method 'Range' of object '_Global' failed.

Any ideas please?

Downes51

 

[MrKowz]

In this case, you have defined the variable "MyRange" as a range object. When you use Range("MyRange"), it's attempting to use the Named Range MyRange, not the variable MyRange.

Try adjusting it to:

Key:=MyRange

 

[Downes51]

Many thanks... I changed the code as follows;

Set MyRange = ActiveSheet.Range(Cells(2, 11), Cells(RowCounter, 11))

Cells.Select
ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort.SortFields. _
Add Key:=MyRange, SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort
.SetRange MyRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With <--------------- Macro now stops here.

But NOW I'm getting the message Run-time 1004... The sort reference is not valid. Make sure it's within the data you want to sort....

I've tried altering the range selection to start at row 1, but it doesn't like that either....

 

[MrKowz]

Hmm... I tried that code and it works for me. I have a feeling it's having issues with the rowcounter variable.

Try using this to define "rowcounter'.

rowcounter = Range("A" & rows.count).End(xlUp).Row

This will use column A to determine where your last row is. However, since you're sorting on column K, it might be better to use that to define your last row. In which case:

rowcounter = Range("K" & rows.count).End(xlUp).Row

 

[Downes51]

That's made a difference, the macro is now executing. BUT the data being sorted is now restricted to Column K. In other words, it's sorting Col K into the right order, but leaving the rest of the data untouched.
Do you think it might be something to do with the 2nd line of this....

With ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort
.SetRange MyRange '<I've tried experimenting with the syntax here, using "=" signs, inverted commas. They all halted the macro.
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Sorry to be a nuisance!

 

[MrKowz]

Yes - the "SetRange" is the entire set you want to sort. What columns do you want sorted?

 

[Downes51]

Yes - the "SetRange" is the entire set you want to sort. What columns do you want sorted?

All of it!

I changed the line to .SetRange = "Selection" (with and without quotes) but it's still not working.

Cells.Select
....
With ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort
.SetRange = "Selection"
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[MrKowz]

Try this out - it uses row 1 to determine how many columns your dataset contains and makes adjustments based on that:

Dim rowcounter      As Long, _
    colcounter      As Long, _
    rngKey          As Range, _
    rngSet          As Range
    
rowcounter = Range("A" & Rows.Count).End(xlUp).Row
colcounter = Cells(1, Columns.Count).End(xlToLeft).Column

Set rngKey = Range(Cells(2, 11), Cells(rowcounter, 11))
Set rngSet = Range(Cells(1, 1), Cells(rowcounter, colcounter))

ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort.SortFields.Add _
    key:=rngKey, _
    SortOn:=xlSortOnValues, _
    Order:=xlDescending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort
    .SetRange rngSet
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

 

[Downes51]

I replied earlier but it seems to have got lost. I fixed it myself.... all it needed was
With ActiveWorkbook.Worksheets("Sales Ledger Aged Debtors Repor").Sort
.SetRange Selection

The VBA Syntax seems quite arbitrary at times!
Thanks VERY MUCH for your help.
Best wishes,
JD