Excel

Sort and Subtotal

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

Justinlabenne

Description:

This will bring up an input box which allows the user to sort a table of data, then subtotal it. 

Discussion:

A fellow coworker needed something to sort his data, then a way to find the totals for each part after sorting. This code helped save some steps, by bypassing the trips to the menu bar to select the features. If the sheet doesn't contain a lot of excess data and formatting, the input-box can be swapped for the "UsedRange" to speed up the process even more. 

Code:

instructions for use 

			
Option Explicit 
 
Sub SortAndSubTotal() 
    Dim SortRng As Range 
     '   Use an Input box to select the range to sort
     '   Assume there are headers for the table
    On Error Resume Next 
    Set SortRng = Application.InputBox("Select the range to sort" & vbCr & _ 
    "Include Headers in the selection", "Sort-Box", 0, , , , , 8) 
     
     '   To avoid using the input-box to select the range to sort,
     '   uncomment you can use the UsedRange property: See below
     '   ???????????????????????????????????????
     '    *Set SortRng = ActiveSheet.UsedRange*
     '   ???????????????????????????????????????
     '   UsedRange can be inconsistent if the data table is constantly
     '   changing or there is formatting in cells not associated with
     '   the table of data. using the input box provides much more
     '   consistent results, but slows the code down because the
     '   range has to be selected.
     
    SortRng.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
     
     '   Select the range for the subtotal dialog-box
    SortRng.Select 
    Application.Dialogs(xlDialogSubtotalCreate).Show 
     
     '   Clear memory
    Set SortRng = Nothing 
End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press (Alt + F11) to open the Visual Basic Editor (VBE).
  4. From the Menu, choose Insert-Module.
  5. Paste the code into the right-hand code window.
  6. Close the VBE (Alt + Q)
  7. Save the file if desired.
 

Test the code:

  1. Put an unsorted, unorganized table of data on a worksheet
  2. Go to Tools > Macro > Macros
  3. Find "SortAndSubtotal"
  4. Highlight it, then press run
  5. When the input-box appears, select the entire table of data
  6. Press ok
  7. The Subtotal dialog-box will appear
  8. Select the options you want an press ok
  9. The table will be sorted, and subtotaled
 

Sample File:

Sort-n-Total.zip 9.4KB 

Approved by mdmackillop


This entry has been viewed 381 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express