Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Sorting
Try recording a macro when you sort your range.
There's an option button on the sort dialog that allows you to specify right to left. I got this (in xl2002): Range("C4:AN4").Select Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal I'd modify it to something like: Option Explicit Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4") With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With End Sub (you really meant sort by column???) Todd Huttenstine wrote: Ive got a worksheet with a table. On row 4 I have different categorys. What is the code I would use for it to look in Row 4 starting with column C and sort alphabetical? My data goes from column C to Column AN in row 4. I would like to have the code inserted into a button so I can just click and it sort. Thank you Todd Huttenstine -- Dave Peterson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Sorting
This almost worked, It sorted the columns but did not move
the data under the columns with the columns when it sorted the alphabetical. How do you fix this? -----Original Message----- Try recording a macro when you sort your range. There's an option button on the sort dialog that allows you to specify right to left. I got this (in xl2002): Range("C4:AN4").Select Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal I'd modify it to something like: Option Explicit Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4") With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With End Sub (you really meant sort by column???) Todd Huttenstine wrote: Ive got a worksheet with a table. On row 4 I have different categorys. What is the code I would use for it to look in Row 4 starting with column C and sort alphabetical? My data goes from column C to Column AN in row 4. I would like to have the code inserted into a button so I can just click and it sort. Thank you Todd Huttenstine -- Dave Peterson . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Sorting
If your data consists of values (not formulas), then I think that this'll work.
I used a sort routine that was posted by Jim Rech and enhanced by Tom Ogilvy. You can find the enhanced version in this thread: http://groups.google.com/groups?thre...40TK2MSFTNGP11 Option Explicit Sub testme02() Dim myRng As Range Dim myArr As Variant Dim LastRow As Long Dim wks As Worksheet Dim dummyRng As Range Set wks = Worksheets("sheet1") With wks Set dummyRng = .UsedRange LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set myRng = .Range("c4:an" & LastRow) myArr = Application.Transpose(myRng.Value) Call QuickSort(myArr, 1, LBound(myArr, 1), UBound(myArr, 1), True) myRng.Value = Application.Transpose(myArr) End With End Sub Sub QuickSort(SortArray, col, L, R, bAscending) ' 'Originally Posted by Jim Rech 10/20/98 Excel.Programming 'Modified by T Ogilvy to sort on first column of a two dimensional array 'Modified by T Ogilvy to handle a a second dimension greater than 1 (or zero) 'Modified by T Ogilvy to do Ascending or Descending Dim i, j, X, Y, mm i = L j = R X = SortArray((L + R) / 2, col) If bAscending Then While (i <= j) While (SortArray(i, col) < X And i < R) i = i + 1 Wend While (X < SortArray(j, col) And j L) j = j - 1 Wend If (i <= j) Then For mm = LBound(SortArray, 2) To UBound(SortArray, 2) Y = SortArray(i, mm) SortArray(i, mm) = SortArray(j, mm) SortArray(j, mm) = Y Next mm i = i + 1 j = j - 1 End If Wend Else While (i <= j) While (SortArray(i, col) X And i < R) i = i + 1 Wend While (X SortArray(j, col) And j L) j = j - 1 Wend If (i <= j) Then For mm = LBound(SortArray, 2) To UBound(SortArray, 2) Y = SortArray(i, mm) SortArray(i, mm) = SortArray(j, mm) SortArray(j, mm) = Y Next mm i = i + 1 j = j - 1 End If Wend End If If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending) If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending) End Sub Todd Huttenstine wrote: This almost worked, It sorted the columns but did not move the data under the columns with the columns when it sorted the alphabetical. How do you fix this? -----Original Message----- Try recording a macro when you sort your range. There's an option button on the sort dialog that allows you to specify right to left. I got this (in xl2002): Range("C4:AN4").Select Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal I'd modify it to something like: Option Explicit Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4") With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With End Sub (you really meant sort by column???) Todd Huttenstine wrote: Ive got a worksheet with a table. On row 4 I have different categorys. What is the code I would use for it to look in Row 4 starting with column C and sort alphabetical? My data goes from column C to Column AN in row 4. I would like to have the code inserted into a button so I can just click and it sort. Thank you Todd Huttenstine -- Dave Peterson . -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Sorting
Option Explicit
Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4").CurrentRegion if myrng.row < 4 then _ set myrng = myrng.offset(4-myrng.row) set myrng = myrng.resize(,38) With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlLeftToRight End With End Sub Assumes you don't have data below your last row that you don't want sorted and there are no completely blank rows below row 4 until the end of the data. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... This almost worked, It sorted the columns but did not move the data under the columns with the columns when it sorted the alphabetical. How do you fix this? -----Original Message----- Try recording a macro when you sort your range. There's an option button on the sort dialog that allows you to specify right to left. I got this (in xl2002): Range("C4:AN4").Select Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal I'd modify it to something like: Option Explicit Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4") With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With End Sub (you really meant sort by column???) Todd Huttenstine wrote: Ive got a worksheet with a table. On row 4 I have different categorys. What is the code I would use for it to look in Row 4 starting with column C and sort alphabetical? My data goes from column C to Column AN in row 4. I would like to have the code inserted into a button so I can just click and it sort. Thank you Todd Huttenstine -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Sorting
Ouch. That one hurt!
Tom Ogilvy wrote: Option Explicit Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4").CurrentRegion if myrng.row < 4 then _ set myrng = myrng.offset(4-myrng.row) set myrng = myrng.resize(,38) With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlLeftToRight End With End Sub Assumes you don't have data below your last row that you don't want sorted and there are no completely blank rows below row 4 until the end of the data. -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... This almost worked, It sorted the columns but did not move the data under the columns with the columns when it sorted the alphabetical. How do you fix this? -----Original Message----- Try recording a macro when you sort your range. There's an option button on the sort dialog that allows you to specify right to left. I got this (in xl2002): Range("C4:AN4").Select Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal I'd modify it to something like: Option Explicit Sub testme01() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("c4:AN4") With myRng .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight End With End Sub (you really meant sort by column???) Todd Huttenstine wrote: Ive got a worksheet with a table. On row 4 I have different categorys. What is the code I would use for it to look in Row 4 starting with column C and sort alphabetical? My data goes from column C to Column AN in row 4. I would like to have the code inserted into a button so I can just click and it sort. Thank you Todd Huttenstine -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting a column | Excel Worksheet Functions | |||
Sorting a Pivot Table Column that is not the first column... | Excel Worksheet Functions | |||
sorting by one column | Excel Discussion (Misc queries) | |||
How do I limit sorting a column to the column? | Excel Worksheet Functions | |||
Right column doesn't change when sorting left column. | Excel Discussion (Misc queries) |