Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
I have quite a lot of rows that i need the rows to be sorted in ascending order
for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
Hi "susielotus"
--If this is a one time activity; sort one row DataSortOptionsLeft to right..and then select the next row and repeat the task using F4.. --If you require this on a regular basis; you may try the below macro which will sort all the rows. The below code sort from the 1st row onwards. You can change that to suit your requirement. Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub --If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: I have quite a lot of rows that i need the rows to be sorted in ascending order for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
The earlier one is too lengthy; try with below
Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, _ MatchCase:=False, Orientation:=xlLeftToRight Next End Sub If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi "susielotus" --If this is a one time activity; sort one row DataSortOptionsLeft to right..and then select the next row and repeat the task using F4.. --If you require this on a regular basis; you may try the below macro which will sort all the rows. The below code sort from the 1st row onwards. You can change that to suit your requirement. Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub --If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: I have quite a lot of rows that i need the rows to be sorted in ascending order for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
Hi Jacob
thank you for your reply much appreciated. I don't know nothing about macros but I did what you said and I got an error when I ran the macro, it said "Compile error" "Syntax error" and the fifth line was highlighted blue. Please help Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Jacob Skaria" wrote: Hi "susielotus" --If this is a one time activity; sort one row DataSortOptionsLeft to right..and then select the next row and repeat the task using F4.. --If you require this on a regular basis; you may try the below macro which will sort all the rows. The below code sort from the 1st row onwards. You can change that to suit your requirement. Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub --If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: I have quite a lot of rows that i need the rows to be sorted in ascending order for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
Hi Jacob
Sorry this is all new to me. I tried your second macro and it worke!!!! Thank you so much for your help, I'll definetely becoming back to this discussion group for help thanks again "susielotus" wrote: Hi Jacob thank you for your reply much appreciated. I don't know nothing about macros but I did what you said and I got an error when I ran the macro, it said "Compile error" "Syntax error" and the fifth line was highlighted blue. Please help Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Jacob Skaria" wrote: Hi "susielotus" --If this is a one time activity; sort one row DataSortOptionsLeft to right..and then select the next row and repeat the task using F4.. --If you require this on a regular basis; you may try the below macro which will sort all the rows. The below code sort from the 1st row onwards. You can change that to suit your requirement. Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub --If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: I have quite a lot of rows that i need the rows to be sorted in ascending order for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
Thanks and you are always welcome !!
If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: Hi Jacob Sorry this is all new to me. I tried your second macro and it worke!!!! Thank you so much for your help, I'll definetely becoming back to this discussion group for help thanks again "susielotus" wrote: Hi Jacob thank you for your reply much appreciated. I don't know nothing about macros but I did what you said and I got an error when I ran the macro, it said "Compile error" "Syntax error" and the fifth line was highlighted blue. Please help Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Jacob Skaria" wrote: Hi "susielotus" --If this is a one time activity; sort one row DataSortOptionsLeft to right..and then select the next row and repeat the task using F4.. --If you require this on a regular basis; you may try the below macro which will sort all the rows. The below code sort from the 1st row onwards. You can change that to suit your requirement. Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub --If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: I have quite a lot of rows that i need the rows to be sorted in ascending order for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting Rows
Hi Jacob
Hoping you'll be able to help again, is there any chance that you can give me another micro but where it will sort the whole worksheet instead of a sheet at a time Thanks "Jacob Skaria" wrote: Thanks and you are always welcome !! If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: Hi Jacob Sorry this is all new to me. I tried your second macro and it worke!!!! Thank you so much for your help, I'll definetely becoming back to this discussion group for help thanks again "susielotus" wrote: Hi Jacob thank you for your reply much appreciated. I don't know nothing about macros but I did what you said and I got an error when I ran the macro, it said "Compile error" "Syntax error" and the fifth line was highlighted blue. Please help Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub "Jacob Skaria" wrote: Hi "susielotus" --If this is a one time activity; sort one row DataSortOptionsLeft to right..and then select the next row and repeat the task using F4.. --If you require this on a regular basis; you may try the below macro which will sort all the rows. The below code sort from the 1st row onwards. You can change that to suit your requirement. Sub Macro1() Dim lngRow As Long, lngLastRow As Long lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow Rows(lngRow).Sort Key1:=Range("A" & lngRow), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub --If you are new to macros set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() If this post helps click Yes --------------- Jacob Skaria "susielotus" wrote: I have quite a lot of rows that i need the rows to be sorted in ascending order for example 1 5 14 26 42 45 4 14 28 36 7 39 5 25 6 27 9 37 12 18 14 26 2 36 25 4 5 34 4 14 28 25 43 1 11 15 14 5 42 26 45 1 I need a formula or something that will get me the rows looking like this 1 5 14 26 42 45 4 7 14 28 36 39 5 6 9 25 27 37 2 12 14 18 26 36 4 4 5 14 25 34 1 11 15 25 28 43 1 5 14 26 42 45 I have tried the normal sorting, but it's not working, please help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting within Rows | Excel Discussion (Misc queries) | |||
Sorting rows | Excel Worksheet Functions | |||
Sorting Rows | Excel Discussion (Misc queries) | |||
Sorting Rows | Excel Worksheet Functions | |||
sorting rows | Excel Worksheet Functions |