CODEWITHSUNDEEP
excelvbavb.netsorting
Meho2016
Meho2016

Reputation: 25

Run time error 1004 the sort reference is not valid?

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

enter image description here

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

Upvotes: 0

Views: 136

Answers (2)

Harun24hr
Harun24hr

Reputation: 36870

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

Upvotes: 0

Darrell H
Darrell H

Reputation: 1886

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

Upvotes: 1

Related Questions