Using VBA to sort by Absolute Value

Hi

When sorting using the standard "DATA - SORT" menu in excel2013, it pops up the data sort box.
The "Sort On" field lists values, cell color, font color, cell icon.

Would be great if you could also list Absolute value as an option.

Is there any way to do this in VBA?
Of course it may require creating a form or object or something.

Alternatively, as a quick solution, is there a way to easily create a macro (and then assign to a button on custom toolbar) which does a sort on absolute value, but without actually creating a column of absolute values I.e. the macro should not need to create a column of ABSOLUTE values and then sort on that column. It should use the values in the specified range.

Any thoughts?

You could just add the column of absolute values and then delete after sorting or hide the column.

Originally Posted by StephenR

You could just add the column of absolute values and then delete after sorting or hide the column.

I thought about that but the code might end up being a little tedious. Was hoping to see if there are any other existing solutions first before going off and trying my hand it.

I want to create a general macro to keep on my own custom toolbar, since I often find that I need to sort on absolute value. So I'll be applying this to ranges of different sizes. Also, this will be done on various excel working papers, so there could be data to the right or left, or also below the range that I need to sort.

I'm thinking that the best thing to do is to use an input box type8, get the "sort range", then use a second input box type 8 to get the column on which to apply the absolute values. Then I can have the code insert a column after the last column in the range, calculate absolute values, sort, then delete the abs-column.

Well there's nothing built in to Excel if that's what you mean by existing. I think the approach you outline sounds about right. Turn off screen updating and nobody will even see anything.