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

>
Want a good read? Try The Summer of His Life (available on Amazon!)

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

 Post Reply Post Reply
Author
Message
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
' http://www.AccessMVP.com/strive4peace
' http://www.RogersAccessLibrary.com/forum/long-crystal_forum71.html
'
   'given: data has no header (exclude it in your parameters)
   '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)

http://msdn.microsoft.com/en-us/library/ff840646.aspx

syntax:
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 microsoft.office.interop.excel.dll)
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.sort%28v=office.11%29.aspx#Y602

the Worksheet object Sort does not work for just a range

Feedback is appreciated, thank you

Warm Regards,
Crystal

Microsoft MVP
remote programming and training

Access Basics by Crystal
http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

http://www.YouTube.com/LearnAccessByCrystal

 *
   (: have an awesome day :)
 *
Warm Regards,
Crystal
Microsoft MVP
Remote Training & Programming
Let's Connect and Build Together

http://www.AccessMVP.com/strive4peace
http://YouTube.com/LearnAccessByCrystal
~have an awesome day ~
Back to Top
Sponsored Links
>
Want a good read? Try The Summer of His Life (available on Amazon!)

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.