Modify Claus code "Sub Array_Var_Column_Sort()"

  • Thread starter Thread starter L. Howard
  • Start date Start date
Hi Garry,
Am Fri, 22 Aug 2014 13:08:13 -0400 schrieb GS:


.Sort.SortFields.Add Key:=.Range(varKey(i)) _
, SortOn:=xlSortOnValues, Order:=xlDescending

delete SortOn:=xlSortOnValues
Then most of the times Sort works well
If not, please send me your example


Regards
Claus B.
Click to expand...

I'm playing with Howard's file...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,
Am Fri, 22 Aug 2014 13:08:13 -0400 schrieb GS:


.Sort.SortFields.Add Key:=.Range(varKey(i)) _
, SortOn:=xlSortOnValues, Order:=xlDescending

delete SortOn:=xlSortOnValues
Then most of the times Sort works well
If not, please send me your example


Regards
Claus B.
Click to expand...

Another issue is that even though your code sets the range, Sheet.Sort
applies to the entire sheet as opposed to just the area defined in
..SetRange!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,
Am Fri, 22 Aug 2014 15:30:09 -0400 schrieb GS:


in the newer version you can add 64 sort keys. But the syntax to do
so brings the disadvantages you found.


Regards
Claus B.
Click to expand...

Thanks! I've been reading up on this and don't see any reason to change
from how I do data sorting now. Maybe I need to play with it some
more...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

Am Fri, 22 Aug 2014 16:05:04 -0400 schrieb GS:
Thanks! I've been reading up on this and don't see any reason to change
from how I do data sorting now. Maybe I need to play with it some
more...
Click to expand...

if you write for the key the whole range instead of first cell then sort
accept the range.

Instead of
..Sort.SortFields.Add Key:=.Range(varKey(i)) _
, Order:=xlDescending
write the whole range

For i = LBound(varKey) To UBound(varKey)
.Sort.SortFields.Add Key:=.Range(varKey(i), Cells(LRow, _
Left(varKey(i), 1))), Order:=xlDescending
Next

This macro works well now for a table from A to I:

Sub SortTest()
Dim LRow As Long
Dim Lcol As Long
Dim i As Long
Dim varKey As Variant

Application.ScreenUpdating = False

varKey = Array("A1", "F1", "I1")

With Sheets("Sheet1")
Lcol = .Cells(1, Columns.Count).End(xlUp).Column
LRow = .Cells(Rows.Count, 1).End(xlUp).Row

.Sort.SortFields.Clear

For i = LBound(varKey) To UBound(varKey)
.Sort.SortFields.Add Key:=.Range(varKey(i), Cells(LRow, _
Left(varKey(i), 1))), Order:=xlDescending
Next

With .Sort
.SetRange Range("A1:I" & LRow)
.Header = xlNo
.MatchCase = False
.Apply
End With
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
With Sheets("Sheet1")
Lcol = .Cells(1, Columns.Count).End(xlUp).Column
Click to expand...

This is not needed.
Why End(xlUp) instead of...

lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
Click to expand...

Also, name varKey is misleading since we're sorting columns within a
specified range. Here's what I came up with so far...


Const sColsToSort$ = "A,C,E"

Sub SheetSortRange(Optional Wks As Worksheet, Optional sCriteria$)
' Sorts all columns in specified range, keeping
' empty cells in place with 1st sort Key.
Dim vCols, n&, lRow, rngToSort As Range

If Wks Is Nothing Then Set Wks = ActiveSheet
If sCriteria = "" Then sCriteria = sColsToSort
vCols = Split(sCriteria, ",")

Application.ScreenUpdating = False: On Error GoTo Cleanup

With Wks
lRow = .UsedRange.Rows.Count: .Sort.SortFields.Clear
Set rngToSort = .Range(Cells(1, vCols(0)), _
Cells(lRow, vCols(UBound(vCols))))

For n = LBound(vCols) To UBound(vCols)
.Sort.SortFields.Add Key:=.Range(vCols(n) & "1"), _
Order:=xlDescending
Next

With .Sort
.SetRange rngToSort: .Header = xlNo: .MatchCase = False
.Apply
End With '.Sort
End With 'Wks

Cleanup:
Application.ScreenUpdating = True
Set Wks = Nothing: Set rngToSort = Nothing
End Sub 'SheetSortRange

Sub Test_SheetSortRange()
SheetSortRange Sheets("Sheet1"), sColsToSort
End Sub

...where this has no effect on data outside rngToSort!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

Am Sat, 23 Aug 2014 11:48:28 -0400 schrieb GS:
This is not needed.
Why End(xlUp) instead of...
Click to expand...

Howard had this error and wondered why he had a wrong result.
I told him to use End(xlToLeft) instead of End(xlUp) ;-)

Your macro works to sort each column.

My suggestions was for sorting in 2 or more levels with the
Sort.Fields.Add method:
First by column A, then by column E and then by column I.
In that case if you enter the key with the whole range address e.g.
Key:=Range("A1:A20") the data outside the table still remains.
If you write Key:=Range("A1") outside data will be sorted too.


Regards
Claus B.
 
Hi Garry,
Am Sat, 23 Aug 2014 11:48:28 -0400 schrieb GS:


Howard had this error and wondered why he had a wrong result.
I told him to use End(xlToLeft) instead of End(xlUp) ;-)
Click to expand...

I did also and so it's just a remnant from the original code! said:
Your macro works to sort each column.
Click to expand...

Yes.., that's what I was trying to sort out.
My suggestions was for sorting in 2 or more levels with the
Sort.Fields.Add method:
First by column A, then by column E and then by column I.
In that case if you enter the key with the whole range address e.g.
Key:=Range("A1:A20") the data outside the table still remains.
If you write Key:=Range("A1") outside data will be sorted too.
Click to expand...

It seemed to me when I looked at your code that we had come to the same
page regarding approach. I do see your point after a reread; ..it makes
sense. -Thanks for pointing this out!

I decided to build this into a reusable routine, but wanted to get the
sorting limited to rngToSort. I just wanted to let you know where it
was at when I stopped yesterday. I'll probably include your suggestion
after I play with it, but won't get back to it for a bit because I'm
busy with modifying the folding ramp of my wheelchair van so it extends
the outer section sooner for parallel parking at curbs.

Ultimately, I want a Bool function so I can trap failure. Also,
sCriteria$ will become vCriteria() so I can pass the ColsToSort list
and control the 'With .Sort' parameters. I'll likely rename this
"SheetSort_Cols" and add "SheetSort_Rng" to simplifying coding for
contiguous data. "SheetSort_Cols" will assume including all data
between 1st/last cols. I already have sort Bool functions for doing
individual cols/rows ("RngSort") and range 'areas' ("BlockSort"). I'll
probably end up duplicating this with "SheetSort_" routines.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
My suggestions was for sorting in 2 or more levels with the
Sort.Fields.Add method:
First by column A, then by column E and then by column I.
In that case if you enter the key with the whole range address e.g.
Key:=Range("A1:A20") the data outside the table still remains.
If you write Key:=Range("A1") outside data will be sorted too.
Click to expand...

Ok, that's what was happening with Howard's code, which is what I was
trying to get away from so only the target data got sorted. I can't
imagine why not sort the entire table unless there were groups of
disconnected data across the columns...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top