Sort Data With Excel Macros

Quickly sort data with this macro that runs when you click a hidden shape at the top of a column. The macro selects the whole data range, to help prevent scrambled data.

Sort Data With Invisible Rectangles

Thanks to Dave Peterson, who wrote this technique.

To see how the macro sorts the data, watch this short video. The writtin instructions are below the video.

Create the Invisible Rectangles

Run the following code once, to create the rectangles at the top of the table. You can change the iCol variable, to match the number of columns in your table.

To run the code, copy it to a regular code module, and edit the code, if necessary, by changing the iCol variable, and starting cell, to match your worksheet.

Then on the Excel Ribbon, click the View tab, and click Macros. Select the SetupOneTime macro, and click the Run button.

Sub SetupOneTime()

'adds rectangle at top of each column
'code written by Dave Peterson 2005-10-22
  Dim myRng As Range
  Dim myCell As Range
  Dim curWks As Worksheet
  Dim myRect As Shape
  Dim iCol As Integer
  Dim iFilter As Integer
  iCol = 7  'number of columns
' 2010-Oct-31 added space for autofilter dropdowns
' set iFilter to 0 if not using autofilter
  iFilter = 12 'width of drop down arrow
  Set curWks = ActiveSheet

  With curWks
    Set myRng = .Range("a1").Resize(1, iCol)
    For Each myCell In myRng.Cells
        With myCell
          Set myRect = .Parent.Shapes.AddShape _
              (Type:=msoShapeRectangle, _
              Top:=.Top, Height:=.Height, _
              Width:=.Width - iFilter, Left:=.Left)
        End With
        With myRect
          .OnAction = ThisWorkbook.Name & "!SortTable"
''        2010-Oct-31 revised to fill shapes in Excel 2007
''          .Fill.Visible = False
          .Fill.Transparency = 1#
          .Line.Visible = False
        End With
    Next myCell
  End With
End Sub

Use a Macro to Sort Data

The following code runs when one of the hidden rectangles is clicked. You can change the TopRow, iCol and strCol variables, to match the layout of your table.

Sub SortTable()
  'code written by Dave Peterson 2005-10-22
  '2006-08-06 updated to accommodate hidden rows
  Dim myTable As Range
  Dim myColToSort As Long
  Dim curWks As Worksheet
  Dim mySortOrder As Long
  Dim FirstRow As Long
  Dim TopRow As Long
  Dim LastRow As Long
  Dim iCol As Integer
  Dim strCol As String
  Dim rng As Range
  Dim rngF As Range

  TopRow = 1
  iCol = 7  'number of columns in the table
  strCol = "A"  ' column to check for last row

  Set curWks = ActiveSheet

  With curWks
    LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
    If Not .AutoFilterMode Then
        Set rng = .Range(.Cells(TopRow, strCol), _
                .Cells(LastRow, strCol))
        Set rng = .AutoFilter.Range
    End If
    Set rngF = Nothing
    On Error Resume Next
    With rng
       'visible cells in first column of range
       Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
    End With
    On Error GoTo 0
    If rngF Is Nothing Then
         MsgBox "No visible rows. Please try again."
         Exit Sub
         FirstRow = rngF(1).Row
    End If
    myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
    Set myTable = .Range(strCol & TopRow & ":" _
        & strCol & LastRow).Resize(, iCol)
    If .Cells(FirstRow, myColToSort).Value _
      < .Cells(LastRow, myColToSort).Value Then
        mySortOrder = xlDescending
        mySortOrder = xlAscending
    End If
    myTable.Sort key1:=.Cells(FirstRow, myColToSort), _
              order1:=mySortOrder, _
  End With

End Sub

Download the Sample File

The zipped sample workbook can be downloaded here:

top of page

Sort Data Tutorials

How to Sort Data in Excel - Basics

Sort Data - Excel 2003 Basics

Pivot Table Sorting

Sort a Row in Excel

Numbers Don't Sort Correctly


Search Contextures Sites







Excel Data Entry Popup List




Last updated: July 17, 2016 3:00 PM
Contextures RSS Feed