1

I'm trying to assign a range to an array (by values) in VBA, and use the sort method on it. I can't sort it in the sheet since it is based on formulas and Excel keeps trying to sort based on the formulas.

So something like this would be succinct, but it doesn't work.

Dim TU As Variant
TU = Range("Thermal_Units").Sort key1:=Range("Thermal_Units").Columns(28), order1:=xlAscending, Header:=xlNo

Does anyone know how to accomplish the assignment and the sort? Or am I forced to just write a sort routine to apply after the assignment?

Improve this question
6
  • 2
    Something like TU = Evaluate("SORT(Thermal_Units,28)") maybe Commented Mar 29, 2024 at 23:11
  • 3
    A walk around solution: copy Range("Thermal_Units") to a new sheet, load into an array after sorting the table, then remove the sheet Commented Mar 29, 2024 at 23:18
  • 2
    If you have MS365, you can try TU = Application.Sort(Range("Thermal Units"), 28, 1). If this doesn't work as expected, you could try TU = Range("Thermal Units").Value: TU = Application.Sort(TU, 28, 1) or use another Variant for the 2nd equation. BTW, descending is -1. Commented Mar 29, 2024 at 23:42
  • 1
    Thanks for the comments. Did this late in the workday here, so I'll be testing these ideas on Monday. Commented Mar 29, 2024 at 23:52
  • Looking forward to your feedback. Commented Mar 29, 2024 at 23:54

1 Answer 1

Reset to default
3

Excel will sort ranges with formulas correctly if you absolutize them (or they can be relative if in the same row).

If you don't want to sort the range directly on the worksheet, you can use Evaluate method mentioned by Tim, or you can use the following code:

Sub adasd()
  Dim a As Variant
  a = WorksheetFunction.Sort(Range("A1:B4"))
End Sub

It works with the snapshot of the range (values).

Then you can put the result back to the worksheet onto the same or another location:

Sub adasd()
  Dim a As Variant
  a = WorksheetFunction.Sort(Range("A1:B4"))
  Range("C3:D6") = a
End Sub

Side note.

There are three same ways to access the worksheet functions:

"Application.Sort" = "Application.WorksheetFunction.Sort" = "WorksheetFunction.Sort", where Sort is one of the available functions (as example).

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

No, the Range object has a Sort Method (as I show in my example). I have no idea what you mean by "estimate the references". I've tested both on the sheet and in VBA and the SORT functionality won't sort the ranges right, it appears to sort them by formula instead of value. I don't expect the WorksheetFunction.Sort to work since it won't work in the sheet, but I will try it when I get to work today.
OMG Excel sorts by values never by formulas! At least, you interpret the observed behavior wrongly.
What do you mean under "No, the Range object has a Sort Method"? "Range.Sort" is the method provided by the application. The VBA language itself has no its own sort method (what is you asked about). Also "WorksheetFunction.Sort" is the same as "Range.Sort".
You can share the sample workbook, and I'll explain you what's going on.
Be patient, I have 3 meetings today and am trying to test everything. I tried the Worksheet Function method and it worked good on the original data and on the copied absolutized data, now I have to see how it works on copied relativized data. Will get back to it this afternoon. I'm so amazed how the WF works better than the Range.Sort method.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.