Selecting a dynamic Range and sorting
 

I have a dynamic Range Starting from Cell B9 and the last column is R
It is not necessary that the last cell in the last column to have a
value.
But the Last row in Column B will have a value.

This is the code i used to select the range and sort

With Sheets("Unassigned Requests")
Range(Range("B9"), Range("B9").End(xlDown)(0)).EntireRow.Select
Selection.Sort Key1:=Range("B9"), Order1:=xlAscending,
Key2:=Range("C9") _
, Order2:=xlAscending, Key3:=Range("E9"), Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
End With

The above code, does not select the last row in the range.
Also at the end, it has the rows selected, can i show it de-selected?

Thanks in Anticipation
Thiaga

Selecting a dynamic Range and sorting
 

It doesn't select the last row because you told it not to with your (0)

This will avoid the selecting so no need to unselect. Note there are
periods entered prior to each range reference so it points back to
Sheets("Unassigned Requests")

With Sheets("Unassigned Requests")
.Range(.Range("B9"), .Range("B9") _
.End(xlDown)).Resize(,17).Sort _
Key1:=.Range("B9"), _
Order1:=xlAscending, _
Key2:=.Range("C9"), _
Order2:=xlAscending, _
Key3:=.Range("E9"), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal,
DataOption3:=xlSortNormal
End With


--
Regards,
Tom Ogilvy




"thiaga" wrote in message
oups.com...
I have a dynamic Range Starting from Cell B9 and the last column is R
It is not necessary that the last cell in the last column to have a
value.
But the Last row in Column B will have a value.

This is the code i used to select the range and sort

With Sheets("Unassigned Requests")
Range(Range("B9"), Range("B9").End(xlDown)(0)).EntireRow.Select
Selection.Sort Key1:=Range("B9"), Order1:=xlAscending,
Key2:=Range("C9") _
, Order2:=xlAscending, Key3:=Range("E9"), Order3:=xlAscending,
_
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal
End With

The above code, does not select the last row in the range.
Also at the end, it has the rows selected, can i show it de-selected?

Thanks in Anticipation
Thiaga

Selecting a dynamic Range and sorting
 

Perfect! Thanks Tom..

Selecting a dynamic Range and sorting
 

Perfect! Thanks Tom..