Search code examples
excelvbasorting

How to import a range into a variant array in Excel VBA and sort using the sort method?

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?

Solution

  • 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).