Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data Sorting Technique swattoo Excel Discussion (Misc queries) 2 October 10th 12 03:46 PM
Data sorting, please can anyone advise on the best function to use? ChemicalJasper Excel Discussion (Misc queries) 3 May 16th 07 01:51 PM
Which sorting technique does Excel use in the sort function? DNALESOR Excel Discussion (Misc queries) 9 February 9th 07 11:26 PM
Which sorting technique does Excel use in the sort function? DNALESOR Excel Programming 2 February 9th 07 05:45 PM
Best Technique to clone worksheet Rich_z[_21_] Excel Programming 4 July 6th 05 07:56 PM


All times are GMT +1. The time now is 11:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"