CODEWITHSUNDEEP
vbaexcel
Kes Perron
Kes Perron

Reputation: 477

Excel VBA sort a range

I've written a macro that opens a CSV file containing new data, copies the contents (minus the header row), and pastes it into the main workbook. Now I need it to also sort the entire dataset (all data in the main sheet) by sample number, given in column A. I've tried 2 different methods:

Set rData = ThisWorkbook.Sheets("Data").Range("A1")
Set rData = Range(rData, rData.End(xlDown).Offset(0, 4))
rData.Sort Key1:=Range("A:A"), Order1:=xlAscending, Header:=xlYes  'Debug points here

This method returns run-time error 1004: "The sort reference is invalid."

Set rData = ThisWorkbook.Sheets("Data").Range("A1")
Set rData = Range(rData, rData.End(xlDown).Offset(0, 4))
ThisWorkbook.Sheets("Data").Sort.SortFields.Clear    'Debug points here
ThisWorkbook.Sheets("Data").Sort.SortFields.Add Key:=Range( _
    "A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ThisWorkbook.Sheets("Data").Sort
    .SetRange Range(rData)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

This method returns run-time error 9: "Subscript out of range."

Upvotes: 1

Views: 2916

Answers (2)

FreeMan
FreeMan

Reputation: 5687

I believe it's in here:

Set rData = Range(rData, rData.End(xlDown).Offset(0, 4))

try this:

Set rData = Range(cells(1,1), cells(4,rData.End(xlDown).Offset(0, 4)))

*Note, I often confuse the row/col order in .cells(), so you'll have to swap them if your range is incorrect.

In either case,

Set rData = ThisWorkbook.Sheets("Data").Range("A1")
Set rData = Range(rData, rData.End(xlDown).Offset(0, 4))

is redundant, at the most you need:

Set rData = Range("a1", rData.End(xlDown).Offset(0, 4))

Update based on comment:

I think the issue is in how you're specifying your range to be sorted. The docs say that you can sort any range, including the whole worksheet. May as well sort the whole sheet since this is the only data you've got on it, correct?

set rdata = thisworkbook.sheets("Data")
rdata.sort (Key1:=rdata.range("A:A"), Order1:=xlAscending, Header:=xlYes)

Upvotes: 0

kolcinx
kolcinx

Reputation: 2233

There are similar questions on SO, with answers that might help you.
'1004': “The sort reference is not valid.”
Excel VBA Run-Time Error 1004

For what I understand the error lies here : Key1:=Range("A:A") maybe change it to Key1:=Range("A1") and to be sure, add a workbook or/and worksheet references to the range as well.

Upvotes: 1

Related Questions