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

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

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

OK I hadn't read your post fully because of the earlier errors I observed.
Now what range are you trying to set exactly?
Seems you are you trying to set the range to all the used cells in the
worksheet?
If yes then
Change the Set statement as under:
Set rgLastEx = .UsedRange

'(note there is a dot before U)

Else let me know what range you want to sort. It doesn't seem to be only
column A
because of [b11] in the Do statement.

Sharad

"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

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

sorry, didn't read your first posting.

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
With Sheets("Existing")
.Range("A1").currentRegion..Sort _
Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess
End With
MsgBox prompt:="Existing Sorted"
Else
' This will never be shown.
MsgBox prompt:="No errors have been detected, please continue", _
Buttons:=vbInformation
End If
Loop
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

I've put the code in but it still bugs out on the sort line, with this error:

1004
The sort ref is not valid. Make sure that its within the data you want to
sort, and the first sort box isn't the same or blank.

If i manually type in the data range as:
set rgLastEx = Range("A1:R37775")
and the sort line as:
rgLastEx.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
then it will sort the data

I have tried Sharad's fix as well with the same problem.

any ideas?

"Tom Ogilvy" wrote:

sorry, didn't read your first posting.

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
With Sheets("Existing")
.Range("A1").currentRegion..Sort _
Key1:=.Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess
End With
MsgBox prompt:="Existing Sorted"
Else
' This will never be shown.
MsgBox prompt:="No errors have been detected, please continue", _
Buttons:=vbInformation
End If
Loop
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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