How do I insert a sort function with VBA, so the whole range gets sorted.
This is the function I want to insert:
=SORT(A2:B" & LastRowB & ",2,-1,FALSE)
What i get is
=@SORT(A2:B3662;2;-1;FALSE)
This one is without the needed spill function
-
Use .formula2..Scott Craner– Scott Craner2020-12-20 20:35:56 +00:00Commented Dec 20, 2020 at 20:35
-
Thank you Scott!Mark– Mark2020-12-20 20:48:21 +00:00Commented Dec 20, 2020 at 20:48
Add a comment
|
1 Answer
For reasons of backwards compatibility, the default behaviour for things like Range("A1").Formula is the non-array, single result function. This is by design and prevents formulas that have been created before Dynamic Arrays existed, so existing VBA solutions won't all of a sudden calculate those functions that are now array aware as arrays.
If you want to get the array version instead of the single version of the formula, you can use
Range("A1").Formula2 = Sort(.....)
