Where am i going wrong with this Sort with Multiple keys

[Sahil123]

Hi All,

Sub Sort()
Dim ws As Worksheet
Dim Lrow As Long
Set ws = Sheets("Sheet1")
Lrow = ws.Range("B" & Rows.Count).End(xlUp).Row
With ws.Range("B4:D" & Lrow)
.Sort.SortFields.Clear
.Sort Key1:=.Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
key2:=.Range("D4"), Order1:=xlAscending, Header:=xlGuess
End With
End Sub

 

[Andrew Poulsom]

You have Order1 twice.

 

[Sahil123]

You have Order1 twice.

Hi Andrew,

Still no luck

Getting error - 1004

Ref not valid, sort by box isnt within data you want to sort or is blan

Sub Sort()
Dim ws As Worksheet
Dim Lrow As Long
Set ws = Sheets("Sheet1")
Lrow = ws.Range("B" & Rows.Count).End(xlUp).Row
With ws.Range("B4:E" & Lrow)
.Sort Key1:=.Range("B4"), Order1:=xlAscending, Header:=xlNo, _
key2:=.Range("E4"), Order2:=xlAscending, Header:=xlNo
End With
End Sub

 

[Andrew Poulsom]

Try:

With ws
    .Range("B4:E" & Lrow).Sort Key1:=.Range("B4"), Order1:=xlAscending, Header:=xlNo, _
        key2:=.Range("E4"), Order2:=xlAscending, Header:=xlNo
End With

B4 applied to B4 is C7; and E4 is F7.

 

[Sahil123]

Try:

With ws
    .Range("B4:E" & Lrow).Sort Key1:=.Range("B4"), Order1:=xlAscending, Header:=xlNo, _
        key2:=.Range("E4"), Order2:=xlAscending, Header:=xlNo
End With

B4 applied to B4 is C7; and E4 is F7.

Hi andrew
thank u code works fab

why didnt mine work? Did i have put .range on its on line?
and not sure what u mean by
B4 applied to B4 is C7; and E4 is F7?

 

[Andrew Poulsom]

You were applying Range("B4") to Range("B4:E" & Lrow). That will return a reference to C7 not B4, ie 1 column to the right and 4 cells down from B4.

 

[Sahil123]

You were applying Range("B4") to Range("B4:E" & Lrow). That will return a reference to C7 not B4, ie 1 column to the right and 4 cells down from B4.

Hi Andrew

still trying to get this

my code and ur code is very similiar

can you please explain which part of the code changes so that instead of referring to correct range, it was offsetting 1 column

struggling to understand as the codes seem identical

 

[Andrew Poulsom]

You had:

With ws.Range("B4:E" & Lrow)

and a sort key of .Range("B4"), which is C7. To see what I mean run this:

Sub Test()
    With Range("B4")
        MsgBox .Range("B4").Address
    End With
End Sub

To return B4 you need .Range("A1").

 

[Sahil123]

You had:

With ws.Range("B4:E" & Lrow)

and a sort key of .Range("B4"), which is C7. To see what I mean run this:

Sub Test()
    With Range("B4")
        MsgBox .Range("B4").Address
    End With
End Sub

To return B4 you need .Range("A1").

Thank you

So the way you did it is the correct way

so am i right in saying all i had to do is was this

With ws
.Range("B4:E" & Lrow)

instead of this

With ws.Range("B4:E" & Lrow)

 

[Andrew Poulsom]

Yes that's right.