if you use the code you show in the click event of a commandbutton on sheet1
(as an example) it will perform the sort on sheet1. If you wanted it to
sort data on sheet2 you would need
Private Sub CommandButton1_Click()
Dim rgLastEx as Range
with Worksheets("Sheet2")
set rgLastEx = .Range("A1:R37775")
rgLastEx.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
End With
End Sub
If the data is set up like a data base (no embedded entirely blank rows or
columns), to dynamically pick up the data
Private Sub CommandButton1_Click()
Dim rgLastEx as Range
with Worksheets("Sheet2")
set rgLastEx = .Range("A1").CurrentRegion
rgLastEx.Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlGuess
End With
End Sub
In fact, the above code is copied from the code module for Sheet1 where it
is working very well.
If I remove the "." from the front of each range, the sort occurs on Sheet1.
If I leave the "." before .Range("A1").CurrentRegion and remove it from
Key1:=Range("A1")
then I get your error.
Post your code.
--
Regards,
Tom Ogilvy
Ian_Limbo said:
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 said:
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
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
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
:
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
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
