# Where am i going wrong with this Sort with Multiple keys

#### Sahil123

##### Well-known Member
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
End With
End Sub

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
You have Order1 twice.

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)
End With
End Sub

Try:

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

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

Try:

Code:
``````With ws
.Range("B4:E" & Lrow).Sort Key1:=.Range("B4"), Order1:=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?

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.

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

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

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

Code:
``````Sub Test()
With Range("B4")
End With
End Sub``````

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

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

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

Code:
``````Sub Test()
With Range("B4")
End With
End Sub``````

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

Thank you

So the way you did it is the correct way

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

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

Yes that's right.

Replies
12
Views
627
Replies
3
Views
412
Replies
11
Views
809
Replies
4
Views
229
Replies
3
Views
293

1,203,060
Messages
6,053,305
Members
444,651
Latest member
markkuznetsov1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back