Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How Do I Modify Pearson's Code to Add "Treat Consecutive Delimiters As One"? | Excel Programming | |||
Can you "duplicate" "copy" listboxes and code to multiple cells? | Excel Programming | |||
Modify code to disable "paste" | Excel Programming | |||
Looking for VB code to test for "RING" , "BUSY" disconnects or other signals | Excel Programming |