Selection Sort problem

[shavenlunatic]

I have code that finds the first empty sell and uses that to select the range of cells that i want to sort. The selection part works fine but when it comes to sort it gives an "Sort reference is not valid" error.

Do

scany = scany + 1

Loop Until Sheet3.Cells(scany, 1) = ""

myrange = "A2:C" & scany - 1

Sheet3.Range(myrange).CurrentRegion.Select
Selection.Sort Key1:=Range("A2"),
Order1:=xlAsscending

________
clueless

 

[Rofeu]

Hi,

I believe there is only 1 s in ascending

Cheers,

Roel

 

[shavenlunatic]

Good point but it errors before it even gets there.

Corrected it anyway,

Still same error

________
clueless

 

[Rofeu]

try activating sheet3 before sorting.

Cheers,

Roel

 

[shavenlunatic]

It gets activated just before this and a blank cell is selected.

When going through the code i see that the range ineed is selected properly. Its just the sort that isnt working

________
clueless

 

[PHV]

You may try something like this:
Sheet3.Range(myrange).Sort Key1:=Sheet3.Range("A2"), Order1:=xlAscending

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rofeu]

I just ran the following:

Sub SortRange()

Dim scany As Long
Dim myrange As String

        Do
        
            scany = scany + 1
            
        Loop Until Sheet2.Cells(scany, 1) = ""

        myrange = "A2:C" & scany - 1
        
        Sheet2.Activate
        Sheet2.Range(myrange).CurrentRegion.Select
        Selection.Sort Key1:=Range("A2"), Order1:=xlAscending
        
End Sub

on the following data:

9	vr
8	bj
7	dhtj
6	fethvs
5	b
4	b
3	jj
2	a erhg
1	aergc

and it works fine for me.

Did you declare your variables? Is your code in a module?

Cheers,

Roel

 

[shavenlunatic]

fixed it


Sheet3.Range(myrange).Select

Selection.Sort _
Key1:=Sheet3.Range("A2"), Order1:=xlAscending, Header:=xlNo

________
clueless

 

[firefytr]

Good lord, why does everyone here want to Select anything at all?!

        Do
        
            scany = scany + 1
            
        Loop Until Sheet3.Cells(scany, 1) = ""

        myrange = "A2:C" & scany - 1

        Sheet3.Range(myrange).CurrentRegion.Sort Key1:=Sheet3.Range("A2"),
        Order1:=xlAsscending

-----------
Regards,
Zack Barresse

 

[PHV]

Zack, please reread my suggestion stamped 11 Jul 06 8:56 ;-)

 

[firefytr]

Yes, PHV, I saw that, wasn't aiming that at you. It was the posts following yours.

-----------
Regards,
Zack Barresse