Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"

This seems to work but I don't trust my judgment on evaluating the outcome.

Trying to apply the row count of each variable length column to the specific column to sort.

Lrow1, 2, 3 show correct row numbers in varRow = Array(LRow1, LRow2, LRow3)

Using A1, C1 & E1 pulled down to various row lengths for testing.

I have gotten a 6 row gap in the sort in column A once in awhile.

Thanks.
Howard


Sub Array_Var_Column_Sort()

Dim LRow1 As Long, LRow2 As Long, LRow3 As Long
Dim LRowX As Long, LCol As Long
Dim i As Long, ii As Long
Dim varKey As Variant
Dim varRow As Variant

Application.ScreenUpdating = False

With Sheets("sheet5")
'Last rows
LRow1 = .Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = .Cells(Rows.Count, 3).End(xlUp).Row
LRow3 = .Cells(Rows.Count, 5).End(xlUp).Row

'Last column
'LCol = .Cells(1, Columns.Count).End(xlLeft).Column
LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

'Sortkeys
varKey = Array("A1", "C1", "E1")
varRow = Array(LRow1, LRow2, LRow3)

.Sort.SortFields.Clear
For i = LBound(varKey) To UBound(varKey)
For ii = LBound(varRow) To UBound(varRow)

.Range(.Cells(1, 1), .Cells(varRow(ii), LCol)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo 'Header:=xlYes

Next
Next
End With
Application.ScreenUpdating = True
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column


Curious why you used this instead of...

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

...as you have in the line above (which contains the wrong direction
constant)! This makes for better self-documenting, consistency, and
code brevity!

Otherwise, I don't see in your code why you occasionally get gaps in
colA. Perhaps a runtime issue? Hard to say without knowing the actual
data you're trying to sort. What's the runtime ratio for it to occur?
You can send me the file if you want to see if it happens for me...

--
Garry

Free usenet 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: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"

On Thursday, August 21, 2014 6:27:13 PM UTC-7, GS wrote:
LCol = Cells.Find(What:="*", After:=[a1], _


SearchOrder:=xlByColumns, _


SearchDirection:=xlPrevious).Column




Curious why you used this instead of...



lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column



..as you have in the line above (which contains the wrong direction

constant)! This makes for better self-documenting, consistency, and

code brevity!



Otherwise, I don't see in your code why you occasionally get gaps in

colA. Perhaps a runtime issue? Hard to say without knowing the actual

data you're trying to sort. What's the runtime ratio for it to occur?

You can send me the file if you want to see if it happens for me...



--

Garry



Hi Garry,

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column


This returns 16384, did not seem to right with only three columns A, C & D on my test sheet. I wondered if Claus was setting the sheet to do any number of columns, but don't know.

As far as the sheet I'm testing on it is a blank new sheet and in column A "a1" and pull down 20-30 rows, same with C and D columns. I then mix the data and run the code. So the sheet is real plain jane.

The gap in col A is not consistent, strange and it could have been something I did and was not aware of. Sorta the reason I posted here to get a critical review of the mods I made.

I have no project for this, just occurred to me to try it since not all columns may be the exact same number of rows.

Claus' code took the column length from a single column and applied it to all.

So low priority for sure.

Howard

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

On Thursday, August 21, 2014 6:27:13 PM UTC-7, GS wrote:
LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column




Curious why you used this instead of...



lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column



..as you have in the line above (which contains the wrong direction

constant)! This makes for better self-documenting, consistency, and

code brevity!



Otherwise, I don't see in your code why you occasionally get gaps in

colA. Perhaps a runtime issue? Hard to say without knowing the
actual

data you're trying to sort. What's the runtime ratio for it to
occur?

You can send me the file if you want to see if it happens for me...



--

Garry



Hi Garry,

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column


This returns 16384, did not seem to right with only three columns A,
C & D on my test sheet. I wondered if Claus was setting the sheet to
do any number of columns, but don't know.

As far as the sheet I'm testing on it is a blank new sheet and in
column A "a1" and pull down 20-30 rows, same with C and D columns. I
then mix the data and run the code. So the sheet is real plain jane.

The gap in col A is not consistent, strange and it could have been
something I did and was not aware of. Sorta the reason I posted here
to get a critical review of the mods I made.

I have no project for this, just occurred to me to try it since not
all columns may be the exact same number of rows.

Claus' code took the column length from a single column and applied
it to all.

So low priority for sure.

Howard


Ok! The file you're playing with IS a project and so if you send it to
me I'll play too!<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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"

On Thursday, August 21, 2014 7:34:01 PM UTC-7, GS wrote:
On Thursday, August 21, 2014 6:27:13 PM UTC-7, GS wrote:


LCol = Cells.Find(What:="*", After:=[a1], _


SearchOrder:=xlByColumns, _


SearchDirection:=xlPrevious).Column








Curious why you used this instead of...








lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column








..as you have in the line above (which contains the wrong direction




constant)! This makes for better self-documenting, consistency, and




code brevity!








Otherwise, I don't see in your code why you occasionally get gaps in




colA. Perhaps a runtime issue? Hard to say without knowing the


actual




data you're trying to sort. What's the runtime ratio for it to


occur?




You can send me the file if you want to see if it happens for me...








--




Garry






Hi Garry,




lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column




This returns 16384, did not seem to right with only three columns A,


C & D on my test sheet. I wondered if Claus was setting the sheet to


do any number of columns, but don't know.




As far as the sheet I'm testing on it is a blank new sheet and in


column A "a1" and pull down 20-30 rows, same with C and D columns. I


then mix the data and run the code. So the sheet is real plain jane.




The gap in col A is not consistent, strange and it could have been


something I did and was not aware of. Sorta the reason I posted here


to get a critical review of the mods I made.




I have no project for this, just occurred to me to try it since not


all columns may be the exact same number of rows.




Claus' code took the column length from a single column and applied


it to all.




So low priority for sure.




Howard




Ok! The file you're playing with IS a project and so if you send it to

me I'll play too!<g



--

Garry



And after more testing the gap is now consistent but is in column C.

Data drops about 8 rows and the text to the right also is moved down several rows.

This is now in the weird category!

https://www.dropbox.com/s/y1sx72rxgs...rop%20Box.xlsm

Howard


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"


Garry,

Try this one, I think I sent wrong link in last post

https://www.dropbox.com/s/ep41xcpsw5...20Box%20X.xlsm

Howard
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Garry,

Try this one, I think I sent wrong link in last post

https://www.dropbox.com/s/ep41xcpsw5...20Box%20X.xlsm

Howard


Using .Find includes your notes in colG (returns 7).

Using .Cells(1, Columns.Count).End(xlToLeft).Column does not include
your notes (returns 5).

So 'as is' your notes are included in the sort so I commented out the
..Find code.

Your loops sort each column by each row count. What is the intent here?

--
Garry

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


  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"

On Thursday, August 21, 2014 8:25:15 PM UTC-7, GS wrote:
Garry,




Try this one, I think I sent wrong link in last post




https://www.dropbox.com/s/ep41xcpsw5...20Box%20X.xlsm




Howard




Using .Find includes your notes in colG (returns 7).



Using .Cells(1, Columns.Count).End(xlToLeft).Column does not include

your notes (returns 5).



So 'as is' your notes are included in the sort so I commented out the

.Find code.



Your loops sort each column by each row count. What is the intent here?



--

Garry


Okay, I did notice 7 in the msgbox pop up, but did not make the connection.

So now I believe the problem with the gap in Col A I mentioned earlier was due to the same problem, where I most likely had some data out side column 5 and below the end rows of C & D. Col A was 30 rows deep and that was the issue for sure.

Mostly what I was trying to do here was practice on arrays. After seeing the columns to sort in an array and I noticed that if the columns were different lengths, all columns would only be sorted down to the last columns length, as it was the value for Lrow for each column.

So the mission was to put each columns Lrow in an array and apply it to the proper column from the column array.

Looks like I got it mostly accomplished but was sidetracked by side show antics of my own doing.

So it still leaves me with the question, suppose there are three columns to sort A C & D. And each a different number of rows. And to the right are many other columns.

Now, it just dawns on me that you don't need the last column line of code, you can use i as the column in:

..Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

Hmmm, haven't tried it but will do so now.

Back in a bit.

Howard
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"



Now, it just dawns on me that you don't need the last column line of code, you can use i as the column in:



.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _

Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo



Hmmm, haven't tried it but will do so now.



Back in a bit.



Well, that did not work. Errors out, I tried i + 1 also, but no go. (with both i's.)

..Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

Howard
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Howard,

Am Thu, 21 Aug 2014 22:43:37 -0700 (PDT) schrieb L. Howard:

.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo


why are sorting with different rows?
Try:

Sub Array_Var_Column_Sort()

Dim LRow As Long
Dim LRowX As Long, LCol As Long
Dim i As Long, ii As Long
Dim varKey As Variant

Application.ScreenUpdating = False

With Sheets("sheet5")
LRow = .UsedRange.Rows.Count
'Last column
'LCol = .Cells(1, Columns.Count).End(xlLeft).Column
LCol = Cells.Find(What:="*", After:=[a1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

'Sortkeys
varKey = Array("A1", "C1", "E1")

.Sort.SortFields.Clear
For i = LBound(varKey) To UBound(varKey)
.Range(.Cells(1, 1), .Cells(LRow, LCol)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo
'Header:=xlYes

Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"

I tried this, which put i to 1 (one) in the code line, but still errors out.

For i = LBound(varKey) To UBound(varKey)
For ii = LBound(varRow) To UBound(varRow)
i = i + 1
.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _
Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo 'Header:=xlYes

Next
Next

Howard
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

So it still leaves me with the question, suppose there are three
columns to sort A C & D. And each a different number of rows. And
to the right are many other columns.


Why not simplify it...

Const sColsToSort$ = "A,C,D"
varKey = Split(sColsToSort, ",")

'''
For i = LBound(varKey) To UBound(varKey)
.Columns(varKey(i)).Sort Key1:=.Cells(1, varKey(i))...

...where there's no need to know row count because blank cells will be
below cells conatining data. So there's really 2 ways to go with
this...

Option Explicit

Const sColsToSort$ = "A,C,E"

Sub Array_Var_Column_Sort2()
Dim vCols, n&

Application.ScreenUpdating = False
vCols = Split(sColsToSort, ",")
With Sheets("sheet1")
' .Cells.SortFields.Clear'//late versions only
For n = LBound(vCols) To UBound(vCols)
.Columns(vCols(n)).Sort Key1:=.Cells(1, vCols(n)), _
order1:=xlAscending, Header:=xlNo 'Header:=xlYes
Next
End With
Application.ScreenUpdating = True
End Sub

Sub Array_Var_Column_Sort3()
Dim vCols, vCol

Application.ScreenUpdating = False
With Sheets("sheet1")
' .Cells.SortFields.Clear'//late versions only
For Each vCol In Split(sColsToSort, ",")
.Columns(vCol).Sort Key1:=.Cells(1, vCol), _
order1:=xlDescending, Header:=xlNo 'Header:=xlYes
Next
End With
Application.ScreenUpdating = True
End Sub

...where the 2nd sub uses a 'virtual' array. Not what you're working on
but thought you might appreciate the similarities.

--
Garry

Free usenet 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: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"

On Thursday, August 21, 2014 10:50:35 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Thu, 21 Aug 2014 22:43:37 -0700 (PDT) schrieb L. Howard:



.Range(.Cells(1, 1), .Cells(varRow(ii), i)).Sort _


Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo




why are sorting with different rows?

Try:



Sub Array_Var_Column_Sort()



Dim LRow As Long

Dim LRowX As Long, LCol As Long

Dim i As Long, ii As Long

Dim varKey As Variant



Application.ScreenUpdating = False



With Sheets("sheet5")

LRow = .UsedRange.Rows.Count

'Last column

'LCol = .Cells(1, Columns.Count).End(xlLeft).Column

LCol = Cells.Find(What:="*", After:=[a1], _

SearchOrder:=xlByColumns, _

SearchDirection:=xlPrevious).Column



'Sortkeys

varKey = Array("A1", "C1", "E1")



.Sort.SortFields.Clear

For i = LBound(varKey) To UBound(varKey)

.Range(.Cells(1, 1), .Cells(LRow, LCol)).Sort _

Key1:=.Range(varKey(i)), order1:=xlAscending, Header:=xlNo

'Header:=xlYes



Next

End With

Application.ScreenUpdating = True

End Sub





Regards

Claus B.



Hi Claus,

I was trying to amend your code to sort columns A C & D if they were each a different number of rows long. Say 30 9 and 15 respectively.

Also, I was assuming many columns to the right of the three to sort which I don't want to sort.

So my exercise was to try to use an array of the column lengths to apply to the correct column from the column array.

Just a practice task trying to learn more about arrays.

Additional data to the right of the three columns causes some problems when using .Find and the Last column line of your uses the column number to the far right.

Here is my test sheet where the code sees the text to the right of the columns and responds to it, instead of just to the three columns.

https://www.dropbox.com/s/ep41xcpsw5...20Box%20X.xlsm

Howard
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

' .Cells.SortFields.Clear'//late versions only

Mis-typed in 2003! Above line should read...

' .Sort.SortFields.Clear'//late versions only

--
Garry

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


  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Howard,

Am Thu, 21 Aug 2014 22:53:16 -0700 (PDT) schrieb L. Howard:

I tried this, which put i to 1 (one) in the code line, but still errors out.


try:

Sub Array_Var_Column_Sort()
'/ Claus

Dim LRow As Range
Dim Lcol As Long
Dim i As Long
Dim varKey As Variant

Application.ScreenUpdating = False

With Sheets("sheet1")

'Last column
Lcol = .Cells(1, Columns.Count).End(xlToLeft).Column

'LCol is not needed because you sort each column in a seperate loop.
'If you look for last column you have to use xlToLeft and NOT xlUp

'Sortkeys
varKey = Array("A1", "C1", "E1")
.Sort.SortFields.Clear
For i = LBound(varKey) To UBound(varKey)
Set LRow = .Cells(Rows.Count, Asc(Left(varKey(i), 1)) -
64).End(xlUp)
.Range(varKey(i), LRow).Sort _
Key1:=.Range(varKey(i)), order1:=xlDescending, Header:=xlNo
'Header:=xlYes
Next
'
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Modify Claus code "Sub Array_Var_Column_Sort()"


Hi Garry and Claus,

Thanks for the schooling, got some good info and sort codes to study.

You guys are great! Appreciate it.

Regards,
Howard
  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi,

Am Fri, 22 Aug 2014 00:19:22 -0700 (PDT) schrieb L. Howard:

Thanks for the schooling, got some good info and sort codes to study.


always glad to help.

If you want to sort the whole table range first by key1 then by key2
then by key3....

try:

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

Application.ScreenUpdating = False

varKey = Array("A1", "C1", "E1")

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

.Sort.SortFields.Clear

For i = LBound(varKey) To UBound(varKey)
.Sort.SortFields.Add Key:=.Range(varKey(i)) _
, SortOn:=xlSortOnValues, Order:=xlDescending
Next

With .Sort
.SetRange Range(Cells(1, 1), Cells(LRow, Lcol))
.Header = xlNo
.MatchCase = False
.Apply
End With
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Nice!
The newer versions are much more robust since Sort was added to
Worksheet...

--
Garry

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


  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Nice!
The newer versions are much more robust since Sort was added to
Worksheet...


Bummer that Sort *still* doesn't work well with numeric sequencing!!

--
Garry

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


  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Fri, 22 Aug 2014 13:08:13 -0400 schrieb GS:

Bummer that Sort *still* doesn't work well with numeric sequencing!!


..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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Fri, 22 Aug 2014 13:08:13 -0400 schrieb GS:

Bummer that Sort *still* doesn't work well with numeric sequencing!!


.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.


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


  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Fri, 22 Aug 2014 13:08:13 -0400 schrieb GS:

Bummer that Sort *still* doesn't work well with numeric sequencing!!


.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.


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


  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Fri, 22 Aug 2014 15:30:09 -0400 schrieb GS:

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!!!


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


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Fri, 22 Aug 2014 15:30:09 -0400 schrieb GS:

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!!!


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


Regards
Claus B.


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


  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

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...


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

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


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


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


  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Sat, 23 Aug 2014 11:48:28 -0400 schrieb GS:

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


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #28   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

Hi Garry,

Am Sat, 23 Aug 2014 11:48:28 -0400 schrieb GS:

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


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


I did also and so it's just a remnant from the original code!<g

Your macro works to sort each column.


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.


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


  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Modify Claus code "Sub Array_Var_Column_Sort()"

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.


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


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
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
How Do I Modify Pearson's Code to Add "Treat Consecutive Delimiters As One"? [email protected] Excel Programming 4 June 14th 06 12:23 AM
Can you "duplicate" "copy" listboxes and code to multiple cells? HotRod Excel Programming 1 September 1st 04 05:03 PM
Modify code to disable "paste" Jonsson[_34_] Excel Programming 0 June 17th 04 09:10 AM
Looking for VB code to test for "RING" , "BUSY" disconnects or other signals BruceJ[_2_] Excel Programming 3 November 20th 03 02:55 AM


All times are GMT +1. The time now is 05:40 PM.

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"