Sorting multiple rows horizontally

[crookesa]

Hi,

I have over 300 rows with a username in the column A followed by 3 fields in the following columns B,C,D.
I want to just sort all 300 rows alphabetically by column B,C,D.

Eg. Cormac, Santa, Atlas, Egg
After search Cormac, Altas, Egg, Santa.

Any ideas how to do this easily in Excel without VB as I am not good as it.

Thanks...

 

[rjwebgraphix]

Is row 1 a header row or does that data start at row 1?

 

[crookesa]

Is row 1 a header row or does that data start at row 1?

The date is row one but I can add is a header which would be username, field1, field2, field3 but I need each row to be sorted on it's own alphabetically from B thru D.

 

[rjwebgraphix]

Without VBA I think it would be very cumbersome. The only way I could get it to work is to highlight the 3 items on each row and sort just those 3 items one row at a time. X300 lines would be 300 sorts and would take you a long time...

or you could just bite the bullet, copy in this code and it will be done in 3 seconds.

Sub sortrows()

Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
    Range("B" & i, "D" & i).Sort Key1:=Range("B" & i), _
                               Order1:=xlAscending, _
                               Header:=xlNo, _
                          OrderCustom:=1, _
                            MatchCase:=False, _
                          Orientation:=xlLeftToRight, _
                          DataOption1:=xlSortNormal
Next i

End Sub

This will start at row 2 through to the end of the sheet.

 

[crookesa]

Without VBA I think it would be very cumbersome. The only way I could get it to work is to highlight the 3 items on each row and sort just those 3 items one row at a time. X300 lines would be 300 sorts and would take you a long time...

or you could just bite the bullet, copy in this code and it will be done in 3 seconds.

Sub sortrows()

Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
    Range("B" & i, "D" & i).Sort Key1:=Range("B" & i), _
                               Order1:=xlAscending, _
                               Header:=xlNo, _
                          OrderCustom:=1, _
                            MatchCase:=False, _
                          Orientation:=xlLeftToRight, _
                          DataOption1:=xlSortNormal
Next i

End Sub

This will start at row 2 through to the end of the sheet.

Thanks very much I'll give it a go not too familiar with VBA so that's where I struggle.
Appreciate the time taken to reply with a solution.

 

[rjwebgraphix]

Thanks very much I'll give it a go not too familiar with VBA so that's where I struggle.
Appreciate the time taken to reply with a solution.

If you're going to be doing much data manipulation in excel, I would suggestion learning as much as you can about VBA. A few vba tricks under your belt can go a long way and save you a whole lot of time. I'm not the greatest at it and often write 25 lines of code where 5 lines will due, but the time saved has been well worth the time learning. 90% of what I do know I learned right here asking one question at a time as I'm in need of doing a specific task.

Once you have the gist of how to write basic things, then google becomes a powerhouse of code finding and modifying for your own needs. I would suggest keeping a file off to the side for useful code, even if you don't need the code right now. The more you write, the more it will come as second nature.

 

[vnasci01]

Without VBA I think it would be very cumbersome. The only way I could get it to work is to highlight the 3 items on each row and sort just those 3 items one row at a time. X300 lines would be 300 sorts and would take you a long time...

or you could just bite the bullet, copy in this code and it will be done in 3 seconds.

Sub sortrows()

Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
    Range("B" & i, "D" & i).Sort Key1:=Range("B" & i), _
                               Order1:=xlAscending, _
                               Header:=xlNo, _
                          OrderCustom:=1, _
                            MatchCase:=False, _
                          Orientation:=xlLeftToRight, _
                          DataOption1:=xlSortNormal
Next i

End Sub

This will start at row 2 through to the end of the sheet.

Hi, this actually answers to an issue I'm having but with two modifications: each field is two words example fiora@melba separated by a "@" and I want it to sort by the letter after the "@" symbol as opposed to the first letter in the string. Additionally the columns go from A to X. How would I modify that VBA to adapt for those things? Thanks a lot!

 

[CaitlinJade]

Thank you so much, this was exactly what I needed

 

[DocAud3]

I have a more complex version of this issue and I'm haven't been able to modify your script properly. I have a file with 1,500+ rows. Columns A-G are fine, I don't want to sort the data in them. Columns H-W contain 1 word text values or they are blank. I want to sort each row alphabetically from left to right starting with Column H.

My problem is that the values in each row vary. A given row that contain values in columns H through N, but columns O through W are blank. There are rows that only contain a value in column H. And there are rows that contain values in all columns (H-W).

How do I modify your script to get it to tolerate this variation and still sort each row?
Thanks!

Without VBA I think it would be very cumbersome. The only way I could get it to work is to highlight the 3 items on each row and sort just those 3 items one row at a time. X300 lines would be 300 sorts and would take you a long time...

or you could just bite the bullet, copy in this code and it will be done in 3 seconds.

Sub sortrows()

Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
    Range("B" & i, "D" & i).Sort Key1:=Range("B" & i), _
                               Order1:=xlAscending, _
                               Header:=xlNo, _
                          OrderCustom:=1, _
                            MatchCase:=False, _
                          Orientation:=xlLeftToRight, _
                          DataOption1:=xlSortNormal
Next i

End Sub

This will start at row 2 through to the end of the sheet.

 

[bigch]

Without VBA I think it would be very cumbersome. The only way I could get it to work is to highlight the 3 items on each row and sort just those 3 items one row at a time. X300 lines would be 300 sorts and would take you a long time...

or you could just bite the bullet, copy in this code and it will be done in 3 seconds.

Sub sortrows()

Dim i As Long
Dim lr As Long

lr = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
    Range("B" & i, "D" & i).Sort Key1:=Range("B" & i), _
                               Order1:=xlAscending, _
                               Header:=xlNo, _
                          OrderCustom:=1, _
                            MatchCase:=False, _
                          Orientation:=xlLeftToRight, _
                          DataOption1:=xlSortNormal
Next i

End Sub

This will start at row 2 through to the end of the sheet.

i need this code but i want to arrange values (i.e. number 3,2,1 in columns A B and C to rearrange by lowest to highest. I would like this formula to duplicate for 1400 rows.