Sort Selection Without Specifying Range

[jmr9642]

Hello,

I am wondering if there is a way to use Excel's custom sort option without specifying a range. As of now the record macro gives me this:

ActiveWorkbook.Worksheets("pivot table current").Sort.SortFields.Add Key:= _
Range("A58:X58"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal

This does what I want it to do, however the Range will not necessarily be the same every time this macro needs to run, so specifying a range here is not what I want.

If need to know: I am custom sorting last row from left to right and expanding selection to affect rest of data.

Thank you for your time in advance!
~JR

 

[Joe4]

If column A always has data for every row with data, you can dynamically determine the last row and sort like this:

Dim lr as Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Worksheets("pivot table current").Sort.SortFields.Add Key:= _
Range("A58:X" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal

 

[jmr9642]

Hi Joe,

Thanks for your response. Based on your code, doesnt the specification of "A58:X" keep it contingent to a range of data only through A58? I tried that code with data going past A58 to about A65 and it gives an error. I could be wrong as I am relatively new to VBA. This is my code right now:

Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Worksheets("pivot table current").Sort.SortFields.Add Key:= _
Range("A60:X" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
'ActiveWorkbook.Worksheets("pivot table current").Sort.SortFields.Add Key:= _
' Range("A58:X58"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
' :=xlSortNormal
With ActiveWorkbook.Worksheets("pivot table current").Sort
.SetRange Range("A60:X" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

 

[jmr9642]

The middle repeating section is commented out fyi

 

[Joe4]

OK, I may have made some incorrect assumptions. Let's try to clarify.
In your original example, you had the range "A58:X58". That is 1 row, and 24 columns. Did you initially intend to only run the sort on 1 row?
How exactly might range be changing each time?
Does anything stay the same (starting row, starting column, ending row, ending column)?

I need to understand the logic for how you are determining which range to run it against.

 

[jmr9642]

So here's the deal,

What I have: I have a set of data that expands 17 rows down and 24 rows accross with information, the last column and row at the end of each are the grand totals.

What I want to do: I want to sort the data from left to right, from largest to smallest, from the last row (grand total), and while expanding the selection so the data in the columns moving around to numerical order move with the grand total they pertain to.

The problem: There are 17 rows this week, but next week there could be 15, 20, 24, etc. It changes dynamically and the sort function that excel macro recorder came up with uses a specified range, thus if the data exceeds that range it wont sort properly.

I am looking to sort without using a range. To have excel detect the last row of data, extend the selection over to the end of the data horizontally, then sort while expanding the selection to cover the rest of the data.

 

[jmr9642]

To answer one part I forgot: The starting cell with data will always be B41. Other than that, the data could change

 

[Joe4]

OK, so it sounds like the starting column ("A") and enduing column ("X") are always the same, and we always want to sort the last row.
So that is a simple enough edit to the code I posted above:

Dim lr as Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Worksheets("pivot table current").Sort.SortFields.Add Key:= _
Range("A" & lr & ":X" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal

 

[jmr9642]

Do I need to change the portion of code below it that actually applies the sort?

With ActiveWorkbook.Worksheets("pivot table current").Sort
.SetRange Range("A58:X" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

 

[Joe4]

Do I need to change the portion of code below it that actually applies the sort?

If you just want to support that one row, then yes, you would need to change this:

.SetRange Range("A58:X" & lr)

to this:

.SetRange Range("A" & lr & ":X" & lr)

like in the other one (hope you see the pattern here and understand what we are doing - simply replacing the row number part of the range with the last row number we found dynamically).