Sorting left to right

[LongBeachGuy]

I am trying to make the following codes work for multiple rows. So far
I can only do one row at a time. Please help.

Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight,
_
DataOption1:=xlSortNormal

How can I make "B2" a variable so that the sort can work it's way down
the sheet?

 

[Gary Keramidas]

watch for wordwrap:

Sub test()
Dim lastrow As Long
Dim i As Long
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To lastrow
With Range("B" & i, Range("B" & i).End(xlToRight))
.Sort Key1:=Range("B" & i), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End With
Next
End Sub

 

[JW]

For future reference, it is considered bad coding practice to make
selects unles absolutely necessary.
As long as you are certain that you want have any blank cells in your
row, then the xlToRight will work. If you think you may have blank
cells, you can fund the last used column in the row by using
cells(i,"IV").End(xlToLeft) where i is the variable storing the row.
Sub LtoRsort()
Dim i As Long
Dim startCol As Integer
startCol = 2
For i = 2 To Cells(Rows.Count, startCol).End(xlUp).Row
With Range(Cells(i, startCol), _
Cells(i, startCol).End(xlToRight))
.Sort Key1:=Cells(i, startCol), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End With
Next i
End Sub

 

[dan dungan]

I'm using excel 2000. This code fails at DataOption1--"variable not
defined"

Dan

 

[JW]

Sub LtoRsort()
Dim i As Long
Dim startCol As Integer
startCol = 2
For i = 2 To Cells(Rows.Count, startCol).End(xlUp).Row
With Range(Cells(i, startCol), _
Cells(i, startCol).End(xlToRight))
.Sort Key1:=Cells(i, startCol), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight
End With
Next i
End Sub

 

[Dave Peterson]

Dataoption# was added in xl2002. Remove it (and the preceding comma) and try it
again.

 

[dan dungan]

Ok, Thanks

Dataoption# was added in xl2002. Remove it (and the preceding comma) and try it
again.