Ok, my understanding is that you want to sort each sheet's entire data
as per the sort order keys. Now I get the impression you want to sort a
list of columns *individually* as an array separate from the rest of
the sheet data. Which is it?
Yes, *individually*, if a column is not in the array then leave that column
alone. And the column selections will be different sheet to sheet.
And some sheets will require no sort at all. But it looks like that is not a
problem if there is no Case that mentions a sheet, it is ignored, skipped
dismissed, or whatever.
Howard
<...sort a list of columns *individually* as an array separate from the rest
of the sheet data...>
For clarity, this is doable one column at a time and not collectively
as a group because non-contiguous range sorting is not supported via
Excel's Sort() function.
What you can do is put each column in the list into an array, sort the
array, then put the array back into the sheet. In the case of sheets
that won't get sorted, their SortCriteria cell is left empty (or
doesn't exist). This must be checked before processing begins.
In the case where you go with this over using hard-coded Select Case,
you can use the SortArray procedure from the other day. Note that each
col will be sorted individually without respect to other cols.
if you really want to sort individually then try:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MyCol As Variant
Dim i As Integer
Dim LRow As Long
Select Case Sh.Name
Case "Sheet1"
MyCol = Array(4, 7, 1, 8, 12)
Case "Sheet2"
MyCol = Array(2)
Case "Sheet3"
MyCol = Array(1, 4, 7, 8, 12)
End Select
For i = LBound(MyCol) To UBound(MyCol)
LRow = Cells(Rows.Count, MyCol(i)).End(xlUp).Row
Range(Cells(1, MyCol(i)), Cells(LRow, MyCol(i))).Sort _
Key1:=Cells(1, MyCol(i)), Order1:=xlAscending, _
Header:=xlYes
Next
End Sub
view. Now, doing the code, I will need help.
So, if we do the "one column at a time" then does it hold true that we can
list columns 1, 3, and 5 to be sorted *individually* on a particular sheet
AND then col 1 could be sorted Ascending, col 2 can be sorted Descending and
col 5 could be Ascending?
I hope that is true, the OP was pretty much all over the board on do's and
don'ts from sheet to sheet etc. So if that can happen that would be a plus I
believe.
Howard
Columns(CLng(v)).Sort Key1:=Cells(1, CLng(v)), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
If you implement a methodology that tells your code what to do then no
problem in specifying sort order.
Example:
[SortCriteria] = "2,1,3:a|4,5:d"
..then split the string by the pipe delimiter to get a 2-element 1D
array. Then split each element by the colon delimiter, then split that
by the comma delimiter...
Dim vSortList, vCols, vSortOrder, v, n&, j&
vSortList = Split([SortCriteria], "|")
For n = LBound(vSortList) To UBound(vSortList)
vCols = Split(vSortList(n), ":")
If vCols(1) = "a" Then vSortOrder = xlAscending _
Else vSortOrder = xlDescending
For Each v In Split(vCols(0))
Columns(CLng(v)).Sort Key1:=Cells(1, CLng(v)), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
Next 'n
..or the like. Personally, I prefer users to enter column labels
because they can just read as when entering col nums it's easy to make
counting mistakes. In this case use the +/- symbols for sort order.
So...
If vCols(1) = "+" Then vSortOrder = xlAscending _
Else vSortOrder = xlDescending
correction? Like maybe For Each v In Split(vCols(0), ",","|")
After some thought, you can simplify the sort order process by adopting
the convention that the left hand list is ascending, the right
descending. This also handles if there's only 1 or the other...
For both: "2,1,3:4,5"
For Ascending only: "2,1,3,4,5:"
For Descending only: ":2,1,3,4,5"
..and handle it like so...
Sub SortCols()
Dim bOrderBoth As Boolean
Dim vSortCriteria, vCols, vSortOrder
'Assume both sort orders
bOrderBoth = True
vSortCriteria = Split([SortCriteria], ":")
If LBound(vSortCriteria) = Empty Then _
bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU
If UBound(vSortCriteria) = Empty Then _
bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL
SortL:
If bOrderBoth Then vSortOrder = xlAscending
For Each v In Split(vSortCriteria(0), ",")
Columns(CLng(v)).Sort Key1:=Cells(1, CLng(v)), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
If Not bOrderBoth Then Exit Sub
SortU:
If bOrderBoth Then vSortOrder = xlDescending
For Each v In Split(vSortCriteria(1), ",")
Columns(CLng(v)).Sort Key1:=Cells(1, CLng(v)), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
End Sub
*Note:* This depicts as if still using col index. If you use col labels
then there's no need for the CLng() function...
[SortCriteria] = "b,a,c,d,e:" --OR-- "b,a,c:d,e" --OR-- ":b,a,c,d,e"
..for Ascending, both, or descending sort order.
Staying with col index which is the column number right?
Code is in a regular sub title of Sub SortCols()
Do I keep that name or is it for "transport" only... Should I delete it and
use the Private Sub Workbook_SheetActivate(ByVal Sh As Object) in the
ThisWorkbook module?
Ran as Sub SortCols() and v was not declared so I tried plain Dim v, Dim v as
Variant, and Dim v as Integer...
Here ya' go!
Here ya' go!
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim vSortCriteria
On Error Resume Next '//if name doesn't exist
vSortCriteria = Sh.Range("SortCriteria").Value
End Sub
Sub SortCols2(Wks As Worksheet, SortCriteria$)
' Sorts individual specified cols
' Args: Wks The worksheet to be sorted
' SortCriteria Delimited string of col labels
' Not case sensitive
' **Note that SortCriteria is multi-delimited
' where sort order is delimited by a colon,
' and col labels by a comma. Left side of colon
' gets sorted ascending; right side descending.
' Examples: sort ascending only: "a,b,c,d,e:"
' sort descending only: ":a,b,c,d,e"
' sort both: "a,b,c:d,e"
Dim vSortCriteria, vCols, vSortOrder, v, bOrderBoth As Boolean
'Assume both sort orders
bOrderBoth = True
'Determine sort order
vSortCriteria = Split(SortCriteria, ":")
If LBound(vSortCriteria) = Empty Then _
bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU
If UBound(vSortCriteria) = Empty Then _
bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL
SortL:
If bOrderBoth Then vSortOrder = xlAscending
For Each v In Split(vSortCriteria(0), ",")
Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
If Not bOrderBoth Then Exit Sub
SortU:
If bOrderBoth Then vSortOrder = xlDescending
For Each v In Split(vSortCriteria(1), ",")
Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
End Sub
Copied and pasted as it is I can pretty much follow what is going on.
Usually enough to ask question that I can't understand the answers. Real
head case sometimes.
I sure do struggle with the higher end codes to assemble though.
I appreciate you vast help and patience.
Regards,
Howard
Did you catch the typo in the _SheetActivate event? I appended 2 to the
sub name as the original was still in my wkb. (I didn't mean for it to
use the 2 when I wrote the event code)
Yes, caught that right away on an error message when I was getting ready to
set up some sheets to test ME and the code.
I do have this question that comes to mind, I have re scanned the code and
can't wring it out to my satisfaction.
With this:
For both: "2,1,3:4,5"
For Ascending only: "2,1,3,4,5:"
For Descending only: ":2,1,3,4,5"
If I want:
Sheet1 like both.
Sheet2 like Asc. only
Sheet3 like Dec. only
There is only one place in the code to enter a sheet sort preference.
Must I change the preference for each sheet? That is, if I enter in the code
the sheet1 preference and then go to sheet 3, won't it sort sheet3 the same
as sheet1, unless I change the preference?
I can grasp the select case where the sheet name and sort preference is pre
set and just waiting to be called into action.
Howard
Define a cell with local scope; name:="SortCriteria" on each sheet to
be sorted. Put the sort criteria in this cell for each sheet
respectively. The _SheetActivate event will check this cell for its
value and pass it to SortCols. Do not enter any sort criteria into the
code as this will nullify its generic attribute that make it reusable.
IOW, the values will be hard-coded and thus a serious programming
no-no! So...
Sheet1!SortCriteria will have comma delimited col labels separated by
a colon.
Sheet2!SortCriteria will have a colon following the comma delimited
col labels.
Sheet3!SortCriteria will have a colon followed by the comma delimited
col labels.
I'm just not sure how your Q relates to my code since the
_SheetActivate event code clearly checks each sheet's
Range("SortCriteria").Value. That's why the cell where the criteria is
stored has local scope; it makes it reusable on all sheets!
OR
Am I misunderstanding your Q?
out of the way of the data ranges, I presume.