VBA Variable Range Sort

[Tony]

I am trying to sort a variable range with a static first cell ("A4"). How do
I use a variable reference to the end range in the sort statement? Here is
what I am trying to use but it gives me a syntax error.

wsPh.Application.Goto Reference:="R4C1"
wsPh.Range("a4"r" & EMcnt)").Select
.Selection.Sort Key1:=Range("I4"), Order1:=xlAscending,
Key2:=Range("R4") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

 

[Jacob Skaria]

Get the last row using

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

and then

wsPh.Range("A4:R" & lngLastRow).Select

 

[Tony]

I am now using the following code which puts in the last row (I am gloing to
be adding more data after this group has been sorted). I now recive an
"method or data member not found" on the next line(.Selection.Sort....):

wsPh.Range("a4:r" & EBottom).Select
.Selection.Sort Key1:=Range("I4"), Order1:=xlAscending, Key2:=Range("R" &
EBottom) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal

 

[Dave Peterson]

Remove the dot in front of .Selection.sort

Or stop the selection:

with wsPh
with .Range("a4:r" & EBottom)
.cells.Sort Key1:=.columns(9), Order1:=xlAscending, _
Key2:=.columns(18), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=false, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
end with
end with

Also, I bet you know if your data has headers. I wouldn't let the code guess.
Use xlyes or xlno.

 

[Bernie Deitrick]

With wsPh
..Range("a4:r" & EBottom).Sort Key1:=.Range("I4"), Order1:=xlAscending,
Key2:=.Range("R" &
EBottom) _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2
_
:=xlSortNormal
End With

HTH,
Bernie
MS Excel MVP