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.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=598
Printed Date: 12 Dec 2017 at 4:55pm


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
' 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 - 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 - 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 - http://www.AccessMVP.com/strive4peace
Free 100-page book that covers essentials in Access

http://www.YouTube.com/LearnAccessByCrystal - 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 ~



Print Page | Close Window