+ Reply to Thread
Results 1 to 4 of 4

Using VBA to sort by Absolute Value

  1. #1
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    125

    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?

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Using VBA to sort by Absolute Value

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

  3. #3
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    125

    Re: Using VBA to sort by Absolute Value

    Quote Originally Posted by StephenR View Post
    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.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    Re: Using VBA to sort by Absolute Value

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 04-08-2013, 05:08 AM
  2. absolute value of sheet to absolute cell sheet?
    By hmac13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2009, 11:31 AM
  3. Absolute references aren't absolute?
    By Sir Spike in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2007, 10:21 PM
  4. [SOLVED] How can I do an Absolute Cell Reference but non Absolute Cell Pais
    By John in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2005, 02:05 PM
  5. Replies: 6
    Last Post: 01-20-2005, 01:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1