Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Long, Crystal
  New Posts New Posts RSS Feed - Code to Sort a Range in Excel - can use in Access
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)

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

 Post Reply Post Reply
Crystal Long View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 21 May 2012
Location: Colorado
Status: Offline
Points: 35
Post Options Post Options   Thanks (0) Thanks(0)   Quote Crystal Long Quote  Post ReplyReply Direct Link To This Post Topic: Code to Sort a Range in Excel - can use in Access
    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 ~
Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down