Sorting left to right

  • Thread starter Thread starter LongBeachGuy
  • Start date Start date
L

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?
 
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
 
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
 
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
 
Dataoption# was added in xl2002. Remove it (and the preceding comma) and try it
again.
 
Back
Top