Your only sorting one cell.
Dim lastRow as long

With Sheets("Existing")
lastRow = .cells(rows.count,1).End(xlup).Row
MsgBox rgLastEx
MsgBox prompt:="Error detected, click ok to attempt a sort fix", _
Buttons:=vbExclamation
.Range(.Range("A1"),.Range("A" & lastRow).Sort _
Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess
MsgBox prompt:="Existing Sorted"
End With

--
Regards,
Tom Ogilvy


"Ian_Limbo" wrote in message
...
I've changed all this items, and i've also tried putting it all into With
statements, but it still won't sort the data. It appears that it does not
like my range, possibly!

The new code i'm trying is:

With Sheets("Existing")
Set rgLastEx = .Range("A1").SpecialCells(xlCellTypeLastCell)
MsgBox rgLastEx
MsgBox prompt:="Error detected, click ok to attempt a sort fix",
Buttons:=vbExclamation
rgLastEx.Sort Key1:=Sheets("Existing").Range("A1"),
Order1:=xlAscending, Header:=xlGuess
MsgBox prompt:="Existing Sorted"
End With

"Sharad Naik" wrote:

As an immediate observervation :-
Change all Range("A1") inside between With and End With to
.Range("A1") (a dot before R).

Then for the 3rd Range("A1") which is not within a With para,
decide which sheet it refers to and change it to
Sheets("That-Sheet-Name").Range("A1")

Sharad

"Ian_Limbo" wrote in message
...
I have a list with a fixed no. of columns and a variable no. of rows.
I
need
to sort this list using a button on another sheet. I am not sure if
the
error is in selecting the range or in my sort code. The code I have so
far
is:

Private Sub CommandButton2_Click()
Do Until [b11] = 0
With Sheets("Existing")
Set rgLastEx = Range("A1").SpecialCells_(xlCellTypeLastCell)
MsgBox rgLastEx
End With
With Sheets("Options")
Set rgLastOp = Range("A1").SpecialCells_(xlCellTypeLastCell)
End With
If [b11] < 0 Then
MsgBox prompt:="Error detected, click ok to attempt a sort
fix",
Buttons:=vbExclamation
rgLastEx.Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:=xlGuess
MsgBox prompt:="Existing Sorted"
Else: MsgBox prompt:="No errors have been detected, please
continue",
Buttons:=vbInformation
End If
Loop
End Sub


Many thanks for any help

Ian