[HW 2.1b] Making It Easier to Sort

In Excel, there are a few ways you can sort a data set.  You can:

  1. Click on Data/Sort then navigate the Sort Data dialog box.
  2. Right click on a highlighted a data set and hit sort.
  3. Apply a filter to a data set, then click on the filter arrows, and then select “Sort A to Z” or “Sort Z to A”.

It would be faster, however, to highlight all the rows of a data set and then have it sort on the active field.

To do that, you would have to do the following:

  • First go to the cell you want to use as the sort key, in the top row of the data that you want to sort
  • Then highlight that column by pressing Shift+End+DownArrow
  • Then highlight the rows to sort by pressing Shift + Spacebar.
  • Then run a macro to sort the rows based on the active field.

Running the macro multiple times should toggle between ascending and descending sort orders.

You can start by recording a macro that records your sorting of a data set, but you may run in to trouble with hardcoded references being placed in your code.  Because of this, you will likely have to find another way to accomplish this task, so that the macro you record here can be useful in other spreadsheets.

Your assignment is to write this macro.

 

Advertisements

One comment

  1. Here’s what I use. I map this to a keystroke combination and find it very useful.

    Sub SortOnActiveField()
    ‘======================================================================
    ‘ This is useful for doing a quick sort of a range of data.
    ‘ First go to the cell you want to use as the sort key, in the top row
    ‘ of the data that you want to sort
    ‘ Then highlight that column by pressing Shift+End+DownArrow
    ‘ Then highlight the rows to sort by pressing Shift + Spacebar.

    ‘ Then with the range selected, run the macro.
    ‘ Running the macro multiple times will toggle between ascending and
    ‘ descending sort orders
    ‘======================================================================
    Dim x As Variant
    x = ActiveWorkbook.Author

    If x = 1 Then
    Selection.Sort Key1:=ActiveCell, Order1:=xlAscending
    ActiveWorkbook.Author = “0”
    Else

    Selection.Sort Key1:=ActiveCell, Order1:=xlDescending
    ActiveWorkbook.Author = “1”
    End If
    End Sub

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: