Print Page | Close Window

Code to Sort a Range in Excel - can use in Access

Printed From: Roger's Access Library
Category: Other Download Libraries
Forum Name: Long, Crystal
Forum Description: Access Basics is designed for those of you with a thirst to understand the fundamentals of Access with programming in mind ... whether you realize it or not.
Printed Date: 14 Nov 2018 at 11:13pm

Topic: Code to Sort a Range in Excel - can use in Access
Posted By: Crystal Long
Subject: Code to Sort a Range in Excel - can use in Access
Date Posted: 04 Jul 2012 at 6:52pm

Code to Sort a Range in Excel

code to sort a range in Excel by columns or rows. Specify worksheet, row numbers, and column letters for easy calling from code.

This code can be called by Access too!

Sub SortTheRange(pWs As Excel.Worksheet _
   , sCol1 As String _
   , sCol2 As String _
   , sSortColRow As String _
   , nRow1 As Long _
   , nRow2 As Long _
   , Optional booSortByColumn As Boolean = True _
' crystal 120704
' strive4peace
   'given: data has no header (exclude it in your parameters)
      '1. worksheet object reference
      '2. first column letter
      '3. last column letter
      '4. sort column letter or row number (as a string)
      '5. first row
      '6. last row
      '7. True = sort by Column. False = Sort by Row
   With pWs
      ' Order: xlAscending =1. Header: xlNo=2
      .Range(sCol1 & nRow1 & ":" & sCol2 & nRow2).Sort _
         Key1:=.Range( _
            IIf(booSortByColumn _
            , sSortColRow & nRow1 _
            , sCol1 & sSortColRow) _
         ) _
         , Order1:=1 _
         , Header:=2 _
         , Orientation:=IIf(booSortByColumn, 1, 2)
   End With
End Sub

Here is a routine I wrote to test it.  You can modify this to test it in your application:

Sub test_SortTheRange()

   'given: data to sort is in cells B1:D8
   'sort by B
   SortTheRange ActiveSheet, "B", "D", "B", 1, 8
   'given: data to sort is in cells F1:M3
   'sort by Row 1
'   SortTheRange ActiveSheet, "F", "M", "1", 1, 3, False
   MsgBox "done"
End Sub

here is a good Help page on MSDN:

Range.Sort Method (Excel) -

Range.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

I found better explanations of the parameters here:

Range.Sort Method 

Namespace: Microsoft.Office.Interop.Excel
Assembly: Microsoft.Office.Interop.Excel (in -

the Worksheet object Sort does not work for just a range

Feedback is appreciated, thank you

Warm Regards,

Microsoft MVP
remote programming and training

Access Basics by Crystal -
Free 100-page book that covers essentials in Access -

   (: have an awesome day :)

Warm Regards,
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together
~have an awesome day ~

Print Page | Close Window