Sort error?

  • Thread starter Thread starter mc
  • Start date Start date
M

mc

I don't know what the difference is between these 2 pieces of code. The
first piece works but the second is giving the error message of "Error: 1004
- The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By Box isn't the same or blank".

First piece of coding:
xl_WksheetData.Range("A1").Select
Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"),
Order1:=xlAscending, Key2:=Range _
("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

2nd piece of coding:
xl_WksheetData.Range("A1").Select
xl_WksheetData.Range("A1:P" & CStr(intLastRow)).Sort Key1:=Range("C2"),
Order1:=xlAscending, Key2:=Range _
("E2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
 
The two pieces of code are essentially identical. The only difference is your
reference to the sheet in your sort, which is completely unnecessary. When
the sheet is omitted as in your first piece of code the default value is
activesheet. Since you did a select immediately prior we know that xl
_WksheetData is the active sheet. To that end my guess is that there is more
to this than you have posted. If I were writing that piece of code here is
what I would have...

With xl_WksheetData
.Range(.Range("A1"), .Cells(.Rows.Count, "P").End(xlUp)).Sort _
Key1:=.Range("C2"), Order1:=xlAscending, _
Key2:=.Range("E2"), Order2:=xlAscending, _
Key3:=.Range("D2"), Order3:=xlAscending, _
Header:=xlYes
End With

A few things to note. I got ride of many of the sort arguments as they are
not necessary. I changed the header from xlGuess to xlYes. I got rid of the
intLastRow as you probably don't need it unless you are using it other
places. I also qualified all of the ranges (note the dot in front of the word
range) which means that every range is explicitly from the sheet
xl_WksheetData. That means that I can run this code even if xl_WksheetData is
not the active sheet.
 
Thanks, it worked.

Jim Thomlinson said:
The two pieces of code are essentially identical. The only difference is your
reference to the sheet in your sort, which is completely unnecessary. When
the sheet is omitted as in your first piece of code the default value is
activesheet. Since you did a select immediately prior we know that xl
_WksheetData is the active sheet. To that end my guess is that there is more
to this than you have posted. If I were writing that piece of code here is
what I would have...

With xl_WksheetData
.Range(.Range("A1"), .Cells(.Rows.Count, "P").End(xlUp)).Sort _
Key1:=.Range("C2"), Order1:=xlAscending, _
Key2:=.Range("E2"), Order2:=xlAscending, _
Key3:=.Range("D2"), Order3:=xlAscending, _
Header:=xlYes
End With

A few things to note. I got ride of many of the sort arguments as they are
not necessary. I changed the header from xlGuess to xlYes. I got rid of the
intLastRow as you probably don't need it unless you are using it other
places. I also qualified all of the ranges (note the dot in front of the word
range) which means that every range is explicitly from the sheet
xl_WksheetData. That means that I can run this code even if xl_WksheetData is
not the active sheet.
Click to expand...
 
Back
Top