Sort multiple rows by data in certain columns

[lbabli]

I want to sort only certain columns (or cells) in a row as follows:

Unsorted...
A B C D E
1 Label3 1 3 4 2
2 Label2 4 1 16 8
3 Label4 2 9 1 6

Desired Sort of cells in columns B, C, D & E...sorting left t
right...
A B C D E
1 Label3 1 2 3 4
2 Label2 1 4 8 16
3 Label4 1 2 6 9

Any idea how this can be done?

Thank yo

 

[Dave Peterson]

If you select B2:E2, you can use Data|sort and click on the Options button.

From there, you can specify that you want to sort by rows.

In code:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1??
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
Set myRng = .Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
myRng.Sort key1:=myRng.Cells(1), order1:=xlAscending, _
Header:=xlNo, Orientation:=xlLeftToRight
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm