Fixing a sorting error for empty rows (error '1004')?

I have many reports that work perfectly once there is a row or two of
data. But when they're empty and one tries to sort, whether by
accident or by a user just trying to see how the buttons work, the
sort brings back an error box.

The example sort code in these reports is of this type:
******************************
Sub SortByDOCKETnumber()
'
Application.Goto Reference:="R1C3"
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
Key2:=Range("D2") _
, Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("C2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End Sub
******************************

The error code is always this:

Run-time error '1004':
Application-defined or object-defined error

and the user is taken down to the last row possible in the sheet <g>.




What would be ideal is to have the user taken to the top of the page
as when one uses ^+home - which takes us to D2 in this example so
"Range("D2").Select" is what could be used for that, that much I can
do - and for a msg box to come up saying something like:

"This sheet is empty. Please enter values."

Is this possible to do adding whatever is needed to the above code?

Thanks.

Hi StargateFen,

If there is no data below C2, your code will select the last cell in the
column (as you have discovered) and will then try to select the next cell
below that. As this is not possible, you receive the encountered error,

Perhaps try replacing:

> Selection.End(xlDown).Select
> ActiveCell.Offset(1, 0).Select

with

If Not IsEmpty(Range("C3").Value) Then
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If

----
Regards,
Norman


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:[email protected]...
>I have many reports that work perfectly once there is a row or two of
> data. But when they're empty and one tries to sort, whether by
> accident or by a user just trying to see how the buttons work, the
> sort brings back an error box.
>
> The example sort code in these reports is of this type:
> ******************************
> Sub SortByDOCKETnumber()
> '
> Application.Goto Reference:="R1C3"
> Selection.Sort Key1:=Range("C2"), Order1:=xlAscending,
> Key2:=Range("D2") _
> , Order2:=xlAscending, Key3:=Range("B2"), Order3:=xlAscending,
> Header:= _
> xlGuess, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom
> Range("C2").Select
> Selection.End(xlDown).Select
> ActiveCell.Offset(1, 0).Select
> End Sub
> ******************************
>
> The error code is always this:
>
> Run-time error '1004':
> Application-defined or object-defined error
>
> and the user is taken down to the last row possible in the sheet <g>.
>
>
>
>
> What would be ideal is to have the user taken to the top of the page
> as when one uses ^+home - which takes us to D2 in this example so
> "Range("D2").Select" is what could be used for that, that much I can
> do - and for a msg box to come up saying something like:
>
> "This sheet is empty. Please enter values."
>
> Is this possible to do adding whatever is needed to the above code?
>
> Thanks.
>
>
>
>