Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
I offered this code to an OP, given his explanation that whenever his users leave a worksheet column B should be sorted. Then it turned out to be multiple columns that needed to be sorted (see .SetRange Range("B:R") in code). That worked in my tests where col B through R would be sorted and col B was the "sort on col". I assume that is the default unless somehow coded differently, which I did not pursue. Then the other shoe fell and it turns out that not every sheet in the workbook requires a sort, in fact some sheets should be excluded of any sort, and at the same time the columns to sort are different across the various sheets that require a sort. Is there a common sense approach to this where the sheet needing NO sort can be excluded while at the same time those that require a sort can sanely be include and the columns to sort be identified? Perhaps a Case Select... Whe Case = Sheet1 Do a sort on XYZ Case = Sheet 25 Do a sort on ABC Case Else Don't do anything End Case Thanks Howard Option Explicit Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) With ActiveSheet.Sort '.SetRange Range("B:R") .SetRange Range("B:B") .Header = xlNo '.Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Well, you can check the sheetname to make sure it's in the
gsSheetsToSort string, right? -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Monday, August 5, 2013 9:45:23 PM UTC-7, GS wrote:
Well, you can check the sheetname to make sure it's in the gsSheetsToSort string, right? -- Garry Because of the large variance from sheet to sheet of what columns need sorting, say sort A, B, C, F on one sheet, then the next sheet is column B through AA need sorted, the next sheet different still, and the exclusion of some sheets altogether, I think I will attempt a select case and define for each sheet what to sort and the Case Else will be the ones excluded. Maybe use the change event Sheet Deactivate to pop up an alert message to run the sort macro vbYes vbNo. Do you see any major no-no's with that approach? Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Mon, 5 Aug 2013 16:30:47 -0700 (PDT) schrieb Howard: Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) with SheetDeactivate you will get a error message. Try another event. What do you know about the sheet? Do you know the column that will be sorted? Then you could try: Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim MyCol As Integer Select Case Sh.Name Case "Sheet1" MyCol = 1 Case "Sheet2" MyCol = 2 Case "Sheet3" MyCol = 4 End Select Sh.UsedRange.Sort key1:=Cells(1, MyCol), _ order1:=xlAscending, Header:=xlGuess End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 12:10:12 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Mon, 5 Aug 2013 16:30:47 -0700 (PDT) schrieb Howard: Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) with SheetDeactivate you will get a error message. Try another event. What do you know about the sheet? Do you know the column that will be sorted? Then you could try: Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim MyCol As Integer Select Case Sh.Name Case "Sheet1" MyCol = 1 Case "Sheet2" MyCol = 2 Case "Sheet3" MyCol = 4 End Select Regards Claus B. Hi Claus, No I don't know the specific columns nor the sheet names. I was hoping to write some plain jane select case macro where the OP would only have to change the column ranges and sheet names to suit his workbook. So with the snippet you posted if in Case "Sheet1" there were column 1, $, 7, 8, 12 that needed sorted what would MyCol = look like? The reason I mentioned sheet deactivate is because the OP wanted to sort upon leaving the sheet. He may need to change his thinking in that regard. I'm not sure what would be best to evoke the sorts. I'm open to suggestions. Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 1:05:39 AM UTC-7, Howard wrote:
On Tuesday, August 6, 2013 12:10:12 AM UTC-7, Claus Busch wrote: Hi Howard, Am Mon, 5 Aug 2013 16:30:47 -0700 (PDT) schrieb Howard: Private Sub Workbook_SheetDeactivate(ByVal Sh As Object) with SheetDeactivate you will get a error message. Try another event. What do you know about the sheet? Do you know the column that will be sorted? Then you could try: Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim MyCol As Integer Select Case Sh.Name Case "Sheet1" MyCol = 1 Case "Sheet2" MyCol = 2 Case "Sheet3" MyCol = 4 End Select Regards Claus B. Hi Claus, No I don't know the specific columns nor the sheet names. I was hoping to write some plain jane select case macro where the OP would only have to change the column ranges and sheet names to suit his workbook. So with the snippet you posted if in Case "Sheet1" there were column 1, $, 7, 8, 12 that needed sorted what would MyCol = look like? The reason I mentioned sheet deactivate is because the OP wanted to sort upon leaving the sheet. He may need to change his thinking in that regard. I'm not sure what would be best to evoke the sorts. I'm open to suggestions. Howard A typo, make that $ a 4 instead. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Tue, 6 Aug 2013 01:10:14 -0700 (PDT) schrieb Howard: A typo, make that $ a 4 instead. can you do a test with your data? Private Sub Workbook_SheetActivate(ByVal Sh As Object) Dim MyCol As Variant Dim i As Integer Select Case Sh.Name Case "Sheet1" MyCol = Array(4, 7, 1, 8, 12) Case "Sheet2" MyCol = Array(2, 8) Case "Sheet3" MyCol = Array(1, 4, 7, 8, 12) End Select Sh.Sort.SortFields.Clear For i = LBound(MyCol) To UBound(MyCol) Sh.Sort.SortFields.Add Key:=Cells(2, MyCol(i)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal Next With Sh.Sort .SetRange Sh.UsedRange .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Tue, 6 Aug 2013 11:09:14 +0200 schrieb Claus Busch: For i = LBound(MyCol) To UBound(MyCol) Sh.Sort.SortFields.Add Key:=Cells(2, MyCol(i)) _ if there are no headers Key has to be Cells(1, MyCol(i)) and Headers=xlNo Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
if there are no headers Key has to be Cells(1, MyCol(i)) and Headers=xlNo Regards With the code in a standard module and since there are no headers, the change as you mentioned to include .Header = xlNo, if I select a sheet, 1, 2, or 3, nothing happens. Isn't a sheet activated by selecting it? Saved the workbook as macro enabled, closed and reopened workbook. For i = LBound(MyCol) To UBound(MyCol) Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) And Headers = xlNo _ , SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal Next With Sh.Sort .SetRange Sh.UsedRange ..Header = xlNo '.Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Howard |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Tue, 6 Aug 2013 04:09:05 -0700 (PDT) schrieb Howard: For i = LBound(MyCol) To UBound(MyCol) Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) And Headers = xlNo _ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^ Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal Next With Sh.Sort .SetRange Sh.UsedRange .Header = xlNo '.Header = xlYes ^^^^^^^^^^^^^ Header=xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With sorry that I confused you :-( Headers is under Sh.Sort Try: Sh.Sort.SortFields.Clear For i = LBound(MyCol) To UBound(MyCol) Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal Next With Sh.Sort .SetRange Sh.UsedRange .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Monday, August 5, 2013 9:45:23 PM UTC-7, GS wrote:
Well, you can check the sheetname to make sure it's in the gsSheetsToSort string, right? -- Garry Because of the large variance from sheet to sheet of what columns need sorting, say sort A, B, C, F on one sheet, then the next sheet is column B through AA need sorted, the next sheet different still, and the exclusion of some sheets altogether, I think I will attempt a select case and define for each sheet what to sort and the Case Else will be the ones excluded. Maybe use the change event Sheet Deactivate to pop up an alert message to run the sort macro vbYes vbNo. Do you see any major no-no's with that approach? Howard I would store the sort criteria/range info for each sheet in a defined name (or named cell) that your procedure reads from so you don't need to include the extra coding (and added overhead) that a Select Case construct entails. This will greatly reduce ongoing code maintenance. For example, in multi-sheet projects I store various UI settings for each sheet as a defined name, and the associated setting in the RefersTo property. The code is generic and so doesn't care what UI settings it's procesing because each sheet has sheet-specific info the procedure reads from and executes against that sheet. Now, in my cAppEvents class module the UI settings are applied when the sheet is activated in UserMode. When in DevMode the UI settings are removed so I can work on the sheet without runtime restrictions in place. Then to test I simply toggle the boolean that controls gbDevMode. When False the project runs in UserMode. The trigger at startup is the existence of a file in the project path folder that I don't distribute to users. The toggle is controlled by a keyboard shortcut which I also don't disclose. Sorry for elaborating so extensively but felt it necessary to explaining the scope of the sheet-specific settings and one aspect of what you can do with this concept. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Given that the sort is user controlled.., I think using a named cell
where the user can input a delimited list of col labels/nums would be the easiest approach. Criteria could be included via a 2nd delimiter OR input to it's own cell. I suggest an instruction to users to make the delimited list in order of preferred sort key order... 2,1,3,4,5:a (sort ascending) 2,1,3,4,5:d (sort descending) ...where col 2 is used as the key for the sort, and the sort order is specified by a 2nd delimiter. Your code can handle this as follows... Dim vSortInfo, vCols, vSortOrder ... ... vSortInfo = Split([SortCriteria], ":") vCols = Split(vSortInfo(0), ",") If vSortInfo(1) = "a" Then vSortOrder = xlAscending _ Else vSortOrder = xlDescending ... ... ...where vCols can be the sort range passed as an array. The rest should be self-explanatory but I'll elaborate if need be! -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
sorry that I confused you :-( Headers is under Sh.Sort Try: Sh.Sort.SortFields.Clear For i = LBound(MyCol) To UBound(MyCol) Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) _ , SortOn:=xlSortOnValues, Order:=xlAscending, _ DataOption:=xlSortNormal Next With Sh.Sort .SetRange Sh.UsedRange .Header = xlNo .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Hi Claus, Okay got it, after a reread I see what you meant now. Made that change, but I cannot get the sheet to "Activate" and fire the code. Doesn't clicking on the sheet tab activate the sheet. I've tried it in the ThisWorkbook module, sheet module and is in standard Module 1 at present. I like the Case "Sheet1" MyCol = Array(4, 7, 1, 8, 12) where I assume the first column, 4 in this Case, will be the sort key. And the Order= in the For I=. Howard |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Tue, 6 Aug 2013 09:54:09 -0700 (PDT) schrieb Howard: Made that change, but I cannot get the sheet to "Activate" and fire the code. Doesn't clicking on the sheet tab activate the sheet. for me it works fine. Please download he https://skydrive.live.com/#cid=9378A...121822A3%21326 the workbook "Sort". This Workbook has headers but the handling is the same. The code belongs to the code module of ThisWorkbook. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 8:33:41 AM UTC-7, GS wrote:
Given that the sort is user controlled.., I think using a named cell where the user can input a delimited list of col labels/nums would be the easiest approach. Criteria could be included via a 2nd delimiter OR input to it's own cell. I suggest an instruction to users to make the delimited list in order of preferred sort key order... 2,1,3,4,5:a (sort ascending) 2,1,3,4,5:d (sort descending) ..where col 2 is used as the key for the sort, and the sort order is specified by a 2nd delimiter. Your code can handle this as follows... Dim vSortInfo, vCols, vSortOrder ... ... vSortInfo = Split([SortCriteria], ":") vCols = Split(vSortInfo(0), ",") If vSortInfo(1) = "a" Then vSortOrder = xlAscending _ Else vSortOrder = xlDescending ... ... ..where vCols can be the sort range passed as an array. The rest should be self-explanatory but I'll elaborate if need be! -- Garry Hi Gary, That seems pretty good to me, if I understand correctly. User enters the columns in a specific cell AND order, minus the (sort ascending/descending), where the order is really only concerned with the FIRST column which will be the sort key. Question: Is that cell then named in the sheet/workbook as "vSortInfo"? (no quotes) 2,1,3,4,5:a (sort ascending) 2,1,3,4,5:d (sort descending) As I mentioned to Claus, I cannot get the code to run from an Activate action. Not sure what I'm doing wrong there. Howard |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 10:02:38 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Tue, 6 Aug 2013 09:54:09 -0700 (PDT) schrieb Howard: Made that change, but I cannot get the sheet to "Activate" and fire the code. Doesn't clicking on the sheet tab activate the sheet. for me it works fine. Please download he https://skydrive.live.com/#cid=9378A...121822A3%21326 the workbook "Sort". This Workbook has headers but the handling is the same. The code belongs to the code module of ThisWorkbook. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 Okay Claus, I'll give it a go and move the code back to the ThisWorkbook module. Thanks, Howard |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 8:33:41 AM UTC-7, GS wrote:
Given that the sort is user controlled.., I think using a named cell where the user can input a delimited list of col labels/nums would be the easiest approach. Criteria could be included via a 2nd delimiter OR input to it's own cell. I suggest an instruction to users to make the delimited list in order of preferred sort key order... 2,1,3,4,5:a (sort ascending) 2,1,3,4,5:d (sort descending) ..where col 2 is used as the key for the sort, and the sort order is specified by a 2nd delimiter. Your code can handle this as follows... Dim vSortInfo, vCols, vSortOrder ... ... vSortInfo = Split([SortCriteria], ":") vCols = Split(vSortInfo(0), ",") If vSortInfo(1) = "a" Then vSortOrder = xlAscending _ Else vSortOrder = xlDescending ... ... ..where vCols can be the sort range passed as an array. The rest should be self-explanatory but I'll elaborate if need be! -- Garry Hi Gary, That seems pretty good to me, if I understand correctly. User enters the columns in a specific cell AND order, minus the (sort ascending/descending), where the order is really only concerned with the FIRST column which will be the sort key. The sort order was/is optional, just so I could show how to process it if stored along with cols lists. Question: Is that cell then named in the sheet/workbook as "vSortInfo"? (no quotes) If you mean the defined named of the cell containing the sort criteria, then in my code it's between the []! Name is "SortCriteria" and it has local scope so it can be reused on all sheets. 2,1,3,4,5:a (sort ascending) 2,1,3,4,5:d (sort descending) As I mentioned to Claus, I cannot get the code to run from an Activate action. Not sure what I'm doing wrong there. If using an event then use the event's ref to the sheet... vSortInfo = Split(sh.[SortCriteria], ":") ...but since you're not using the sort order then just use the value stored... Dim sColsToSort$ 'as string sColsToSort = sh.[SortCriteria] ...which, using my previous example, returns the delimited string "2,1,3,4,5" of the cols to be sorted. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
This Workbook has headers but the handling is the same. The code belongs to the code module of ThisWorkbook. Hi Claus, Moved the code to the ThisWorkbook module and it fires each time a sheet tab is selected. A puzzling aspect is now, it sorts EVERY column on the sheet despite having say Case "Sheet1" MyCol = Array(1, 2, 3, 4, 5) It sorts 6 on to whatever has data in it. I'm using about 10 to 12 columns in my tests. Near as I can tell the only thing the code obeys is the first column as the "sort on" column. I put in 1 to 9 in column A and pulled that over to column P, and manually toggle back and forth to Ascending/Descending in the code and it sorts A through P each time sheet is selected even though 1 to 5 are in the Array. So it seems the array does not exclude any column after all. It really only sets the "sort on" column. Howard |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Tue, 6 Aug 2013 12:14:20 -0700 (PDT) schrieb Howard: A puzzling aspect is now, it sorts EVERY column on the sheet despite having say Case "Sheet1" MyCol = Array(1, 2, 3, 4, 5) after activating the sheet select a cell into the table and select Data = Sort and you see that the only sort keys are the keys in the array. The data sets will not be destroyed. So it looks like the whole sheet has been sorted. Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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? -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 12:31:28 PM UTC-7, GS wrote:
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... |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 12:31:28 PM UTC-7, GS wrote:
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. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Hi Howard,
Am Tue, 6 Aug 2013 12:53:27 -0700 (PDT) schrieb Howard: 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. 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 Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Example...
Sub SortCols() Dim vCols, n& vCols = Split([sortcriteria], ",") For n = LBound(vCols) To UBound(vCols) Columns(CLng(vCols(n))).Sort Key1:=Cells(1, CLng(vCols(n))), _ Order1:=xlAscending, Header:=xlNo, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next End Sub -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 1:05:56 PM UTC-7, GS wrote:
On Tuesday, August 6, 2013 12:31:28 PM UTC-7, GS wrote: 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? 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. -- Garry Okay, I believe I am getting a grip on all this from the concept point of 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 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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 Hi Claus, Yes, Yes that really seems to do the trick. I'll test it some more but that is what I was visualizing as the way it should work. In response to Garry I made the possible assumption that as long as the columns are sorted individually, perhaps the Ascending/Descending could be specific to each column. Not sure if it's worth the effort, however. Probably falls into the "nice to have, but not necessary" category. Howard |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Okay, I believe I am getting a grip on all this from the concept point of
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 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 -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Oops! I forgot to include the delimiter...
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 -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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 Okay, I'm getting most of this, although I am pretty good at screwing stuff up when putting the code to work.<g I think I'll start with the "a" and the "d" for sort orders. Where you say you forgot the delimiter and then show this: For Each v In Split(vCols(0), ",") Does the "|" pipe delimiter also need to be in the "woops I forgot..." correction? Like maybe For Each v In Split(vCols(0), ",","|") Or is it a default thing when used in this situation? Howard |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Does the "|" pipe delimiter also need to be in the "woops I forgot..."
correction? Like maybe For Each v In Split(vCols(0), ",","|") There is no pipe delimiter at this point. (I guess there's some struggle with understanding arrays going on) The only delimiter in the vCols(0) element is the comma, none in vCols(1). I'm posting a simplified concept shortly... -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 4:48:48 PM UTC-7, GS wrote:
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. -- Garry Okay,I officially "have fallen and cannot get up". 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... Error is ...control variable must be as Variant or Object. The good news is I understand this: [SortCriteria] = "b,a,c,d,e:" --OR-- "b,a,c:d,e" --OR-- ":b,a,c,d,e" Howard |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Okay,I officially "have fallen and cannot get up".
Staying with col index which is the column number right? Cols and rows have an Index property of data type Long. This is the number for each, respectively. (Columns(1) label is "A"; Columns(27) label is "AA") 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? I'd keep Sub SortCols() in a standard module so it's reusable, and simply add args for the sheet ref and sort criteria. Since you don't seem to be able to piece a solution together on your own I'll post (shortly) turnkey code that you can copy/paste... Ran as Sub SortCols() and v was not declared so I tried plain Dim v, Dim v as Variant, and Dim v as Integer... Sorry about that! I forgot to add it when I did my copy/paste stuff. -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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 If Not vSortCriteria = "" Then Call SortCols(Sh, vSortCriteria) 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 -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 6:32:36 PM UTC-7, GS wrote:
Here ya' go! 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 |
#36
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
Oops! Major typo...
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 If Not vSortCriteria = "" Then Call SortCols2(Sh, vSortCriteria) 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 -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#37
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 6:32:36 PM UTC-7, GS wrote:
Here ya' go! 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 Be patient with yourself and you'll do alright. I appreciate the feedback... 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) -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#38
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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) -- Garry 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 |
#39
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
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) -- Garry 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? -- Garry Free uenet access at http://www.eternal-september.org Classic VB Users Regroup comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#40
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet sorting code/technique advise
On Tuesday, August 6, 2013 9:09:45 PM UTC-7, GS wrote:
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) -- Garry 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? -- Garry Ah-ha, okay I'll see if I can get that done. This would be a cell somewhere out of the way of the data ranges, I presume. Thanks, again. |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Sorting Technique | Excel Discussion (Misc queries) | |||
Data sorting, please can anyone advise on the best function to use? | Excel Discussion (Misc queries) | |||
Which sorting technique does Excel use in the sort function? | Excel Discussion (Misc queries) | |||
Which sorting technique does Excel use in the sort function? | Excel Programming | |||
Best Technique to clone worksheet | Excel Programming |