Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
I need to create a macro to sort 659 rows by 46 columns. The rows contain
numeric values that need to be sorted for lowest (left) to highest (right). When I sort manually left to right, the values for every row are not sorted correctly. Additionally when I sort manually, the left column in some rows are blank. This is a spreadsheet that has cells that have been manually updated. Macro below Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:AZ673").Select Range("AZ673").Activate Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub All help is greatly appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending,
Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Column G should be the lowest value when sorting is complete.
"D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
This worked on a smaller test range.
Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Gord,
I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Note the For Each range in my version is one column only.
Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
I'm sure I'm not explaining my request to be helped correctly. Below is an
example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Your explanation was clear....my testing was not good.
This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Gord,
I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
I tried to set it up with your data range being G15:AZ673
The instructions given by Tom........... 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide have been used in the code I provided. Does it not do what you want? Gord On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow wrote: Gord, I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
My assumption was that row 673 in column G would be the last row with data.
If different, post back. Gord On Sun, 02 Dec 2007 19:31:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote: I tried to set it up with your data range being G15:AZ673 The instructions given by Tom........... 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide have been used in the code I provided. Does it not do what you want? Gord On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow wrote: Gord, I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Gord,
I just applied the macro and it worked perfectly. I misunderstood your previous message that it was an example, not the actual macro. Thanks! "Gord Dibben" wrote: I tried to set it up with your data range being G15:AZ673 The instructions given by Tom........... 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide have been used in the code I provided. Does it not do what you want? Gord On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow wrote: Gord, I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
Great.
Thanks for the feedback and to Tom for the original. Gord On Sun, 2 Dec 2007 20:00:01 -0800, Carlton A. Barlow wrote: Gord, I just applied the macro and it worked perfectly. I misunderstood your previous message that it was an example, not the actual macro. Thanks! "Gord Dibben" wrote: I tried to set it up with your data range being G15:AZ673 The instructions given by Tom........... 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide have been used in the code I provided. Does it not do what you want? Gord On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow wrote: Gord, I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
I don't the it will exceed 700 rows if your adjust for 700 that would be great.
"Gord Dibben" wrote: My assumption was that row 673 in column G would be the last row with data. If different, post back. Gord On Sun, 02 Dec 2007 19:31:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote: I tried to set it up with your data range being G15:AZ673 The instructions given by Tom........... 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide have been used in the code I provided. Does it not do what you want? Gord On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow wrote: Gord, I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to sort numeric rows left to right
You don't need adjusting.
The line Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row adjusts for added rows. Gord On Sun, 2 Dec 2007 20:30:01 -0800, Carlton A. Barlow wrote: I don't the it will exceed 700 rows if your adjust for 700 that would be great. "Gord Dibben" wrote: My assumption was that row 673 in column G would be the last row with data. If different, post back. Gord On Sun, 02 Dec 2007 19:31:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote: I tried to set it up with your data range being G15:AZ673 The instructions given by Tom........... 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide have been used in the code I provided. Does it not do what you want? Gord On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow wrote: Gord, I appreciate the responses. I'm not clear as to what changes I need to make for the last macro example to with my spreadsheet. "Gord Dibben" wrote: Your explanation was clear....my testing was not good. This from Tom Ogilvy will work. Sub SortRows() 'Tom Ogilvy macro Dim r As Long Dim Lrow As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'Make the r = 1 whatever the first row of data you want to sort on is. 'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit 'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide For r = 15 To Lrow 'row 15 to last row With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ .Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal End With Next r Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub Gord On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow wrote: I'm sure I'm not explaining my request to be helped correctly. Below is an example range of the data to be sorted g15 h15 i15 j15 k15 l15 0.1830 0.1759 0.1980 0.1890 0.1884 0.1945 0.0380 0.0385 0.0332 0.0340 0.0349 0.0346 0.0565 0.0920 0.0610 After sorting correctly, the range should appear like below g15 h15 i15 j15 k15 l15 0.1759 0.1830 0.1884 0.1890 0.1945 0.1980 0.0332 0.0340 0.0346 0.0349 0.0380 0.0385 0.0565 0.0610 0.0920 "Gord Dibben" wrote: Note the For Each range in my version is one column only. Select the range to sort G15:AZ673 but the sort key is column G You must change your For Each range to For Each cell In Range("G15:G673") Gord On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow wrote: Gord, I modified your macro to include my range and when I run the macro it just loops and never finishes. Also none of the cells in the range are sorted correctly. The modifications I made are below. Range("G15:AZ673").Select For Each cell In Range("G15:AZ673") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Gord Dibben" wrote: This worked on a smaller test range. Sub SortLefttoRight() ' ' SortLefttoRight Macro ' Macro recorded 12/1/2007 by Carlton A. Barlow ' ' Range("G15:O31").Select For Each cell In Range("G15:G31") Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Gord Dibben MS Excel MVP On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow wrote: Column G should be the lowest value when sorting is complete. "D." wrote: Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub is column G the primary column? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Left-Right & Up-Down | Excel Worksheet Functions | |||
How do I create a Macro to sort data and insert blank rows & subto | Excel Worksheet Functions | |||
sorting alpha numeric list by first left digit | New Users to Excel | |||
Sort Macro to Exclude Blank Rows? | Excel Worksheet Functions | |||
Sort Left to Right | Excel Discussion (Misc queries) |