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: 29 Mar 2024 at 2:55am Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com
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 ~
|
|