1

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."

Improve this question
4
  • Which row is throwing the error? Commented Mar 30, 2015 at 18:58
  • I've added comments to the code to show which lines throw errors. Also, I had the wrong error code for Method 2, so that's been updated. Commented Mar 30, 2015 at 19:05
  • Try Set rData = Sheets("Data").Range("A1:D" & Sheets("Data").UsedRange.Rows.Count) Get Rid of Set rData = Range(rData, rData.End(xlDown).Offset(0, 4)) Commented Mar 30, 2015 at 19:13
  • That would work in the short term, but I want this macro to work for data with an unknown number of columns. .Offset(0, 4) would be replaced with .Offset(0, iCol) where iCol is an integer defined by a counting loop prior to this. Commented Mar 30, 2015 at 19:17

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Adding the workbook and sheet reference to the range did the trick. Thanks!
0

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)
Improve this answer

2 Comments

I don't see how my code is redundant. The macro has 2 workbooks open, so I need my code to point to the correct book. Also, I don't see how your last line could possibly work. How can you .End(xlDown).Offset(0, 4)) from a variable that hasn't been set yet?
Yeah, OK, valid points. I was looking at the first range of your second Set rData = statement, and missed seeing the 2nd use of rData in there. I'll take a second crack at it...

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.