Application-defined or object-defined error

[Guest]

Hello,

I asked this question in "excel application errors " section but didn't get
any answers so maybe I have more luck here.

I made a script that sorts a range of cells and deletes rows that do not
comply with a condition. I get this error in Excel 2003 but I don't get it in
Excel 2007.
The debug highlights this line as location of the error:

ActiveSheet.Range(ActiveSheet.Range(first.Offset(stp, 0)),
ActiveSheet.Range(last.Offset(-1, 66))).Select

and the line is part of this context :

rownum = last.Row - first.Offset(stp, 0).Row
i = 1
Do While i <= rownum
Set rowx = ActiveSheet.Range(last.Offset(-i, 0), last.Offset(-i,
66))
If Application.WorksheetFunction.Max(rowx) = 0 Then
rowx.Delete shift:=xlShiftUp
rownum = rownum - 1
Else: i = i + 1
End If
Loop

ActiveSheet.Range(ActiveSheet.Range(first.Offset(stp, 0)),
ActiveSheet.Range(last.Offset(-1, 66))).Select
Selection.sort Key1:=Range(first.Offset(stp, 0), last.Offset(-1, 0)),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This is a sorting script recorded with the macro recorder. first and last
are 2 range type variables which are set when the sub is called by another
sub, stp is an integer set also when the sub is called (it's value may be 2
or 3).
rownum and i are local integers and rowx is a local range (defined in this
sub).

I tried the 2007 sort method and the 2003 sort method and got errors but
just in Excel 2003, they both work ok n Excel 2007. If you have any ideea
how I could make this error dissapear in Excel 2003, please help.

Thank you,

 

[Guest]

I would check the validity of your range references at that point:

rownum = last.Row - first.Offset(stp, 0).Row
i = 1
Do While i <= rownum
Set rowx = ActiveSheet.Range(last.Offset(-i, 0), last.Offset(-i,
66))
If Application.WorksheetFunction.Max(rowx) = 0 Then
rowx.Delete shift:=xlShiftUp
rownum = rownum - 1
Else: i = i + 1
End If
Loop

ActiveSheet.Range(ActiveSheet.Range(first.Offset(stp, 0)),
msgbox first.Offset(stp,0).Address & ", " & last.Offset(-1,0).Address _
& Selection.Address

ActiveSheet.Range(last.Offset(-1, 66))).Select
Selection.sort Key1:=Range(first.Offset(stp, 0), last.Offset(-1, 0)),
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Make sure the ranges are valid and contained within the selection.

 

[Guest]

Hello,

Sorry for the late reply.

I cannot insert the lines as you did, the msgbx command can be inserted
either before the activesheet.range select or after.

Inserting before, it displays the cells which I want to refer to (the
offsets are correct) but not the selection that I want to make (the selection
line is after the msgbox line)
Inserting after, well, the compiler never gets to it, it stops to the
selection line.
Even more, now the method no longer works in Excel 2007 either. And it's
basically the same code as it used to be.

Please answer as soon as you can. Thank you,

Regards,