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

--


Gary


"LongBeachGuy" wrote in message
ps.com...
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?

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

LongBeachGuy wrote:
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?

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

Dan

On Oct 9, 11:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

--

Gary

"LongBeachGuy" wrote in message

ps.com...

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?

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
dan dungan wrote:
I'm using excel 2000. This code fails at DataOption1--"variable not
defined"

Dan

On Oct 9, 11:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

--

Gary

"LongBeachGuy" wrote in message

ps.com...

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?

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

dan dungan wrote:

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

Dan

On Oct 9, 11:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

--

Gary

"LongBeachGuy" wrote in message

ps.com...

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?

--

Dave Peterson

Ok, Thanks

On Oct 9, 1:56 pm, Dave Peterson wrote:
Dataoption# was added in xl2002. Remove it (and the preceding comma) and try it
again.



dan dungan wrote:

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

Dan

On Oct 9, 11:07 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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

--

Gary

"LongBeachGuy" wrote in message

ups.com...

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?

--

Dave Peterson