Run time error 1004 the sort reference is not valid?

Question

I am running an Excel Macro from vb.net for sorting range of data and I receive this error as in the attached image

My Code is

Dim ws As Worksheet
Dim lastRow As Long
Set ws = Worksheets("Result_T10")
lastRow = Worksheets("Result_T10").Range("C2" & Cells(Rows.count, "C").End(xlUp).Row)
with ws
ws.Range("C2" & lastRow).Sort Key1:=Range("C2" & lastRow), Order1:=xlAscending, Header:=xlYes
End With

So What is wrong in the code?

Thanks, Regards

Moheb Labib

Don't forget the `:` when doing a string mention to a range. Ranges are like `C2:C10`, `A1:A20` and so on. Also, check https://stackoverflow.com/a/38882823/9199828 — Foxfire And Burns And Burns, Aug 27 '20 at 11:05

score 1 · Answer 1 · answered Aug 27 '20 at 11:06

You are treating the last row as an entire range and adding to the range. You need to isolate the last row as a row number then create the range.

Dim ws As Worksheet
Dim lastRow As Long
Set ws = Worksheets("Result_T10")
lastRow = ws.Cells(Rows.count, "C").End(xlUp).Row
with ws
ws.Range("C2:C" & lastRow).Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes
End With

score 0 · Answer 2 · answered Aug 27 '20 at 11:06

0

Wrong in this line

lastRow = Worksheets("Result_T10").Range("C2" & Cells(Rows.count, "C").End(xlUp).Row)

As lastRow is Long data type it can store number value only. Your current line returning a range not row. So add .row at the end of line like below.

lastRow = Worksheets("Result_T10").Range("C2" & Cells(Rows.count, "C").End(xlUp).Row).Row

You can simplify this line with

lastRow = ws.Cells(Rows.count, "C").End(xlUp).Row

answered Aug 27 '20 at 11:06

Harun24hr

30,391
4
21
36

@ Harun24HR, I adjusted the lastRow as wrote but I still have the same error – Meho2016 Aug 27 '20 at 11:52
Can you put a screenshot of your data in sheet? – Harun24hr Aug 27 '20 at 12:05

Run time error 1004 the sort reference is not valid?

2 Answers2