Excel 2000 VBA Sort without Select

M

Matt.

Hi all!

If it is possible, can somebody give me the syntax for sorting a worksheet
without selecting it?

The worksheet has a header row (row 1). I would like to sort all the values
in the worksheet by column A Ascending. Column A is a Date. There will
never be more than 50 rows and 10 columns.

This is the code I'm using.

Sheets("Accum").CurrentRegion.Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

The error I'm getting is the method or object isn't supported.

Any help greatly appreciated. Thanks in advance.

cheers,
Matt.
 
M

Matt.

Hi all!

I've changed my sort code to as follows. Now I'm getting an error that
states:
The sort reference is not valid. Make sure that it's within the data you
want to sort, and the first Sort By box isn't the same or blank.

Sheets("Accum").Range("A2").CurrentRegion.Sort Key1:=Range("A2"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Again, any help greatly appreciated. Worksheet setup still the same.

Thanks again for any help.

cheers,
Matt.
 
P

pikus

Dim ws As Worksheet
See if this helps. I'll be glad to explain how it works if you need m
to. - Pikus

Set ws = Worksheets(1)

For x = 2 To 50
alph = ws.Cells(x, 1).Value
a = x + 1
z = x
For y = a To 600
If ws.Cells(y, 1).Value < alph And ws.Cells(y, 1).Value <> "
Then
alph = ws.Cells(y, 1).Value
z = y
End If
Next y

If z <> x Then
ws.Rows(z).Cut
ws.Rows(x).Insert
End If

Next
 
C

Chip Pearson

Matt,

The problem is that the Range in Key1 points to the ActiveSheet,
not the Accum sheet. Try rewriting the code as follows.

With Sheets("Accum")
.Range("A2").CurrentRegion.Sort Key1:=.Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
End With


Note the period before both Range("A2") references.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
M

Matt.

Thanks Pikus.

I understand the sorting algorithm. I just was hoping to use Excel's built
in method without using .SELECT or .ACTIVATE, because everything I've read
says the Selecting elements unnecessarily before working with them slows the
app down. But an algorithm like this will probably be just as slow as
selecting or activating the sheet I want to sort anyway. If my assumption
is wrong, please let me know.

cheers,
Matt.
 
M

Matt.

Thanks Chip!

This is exactly what I was searching for. (To anybody else who might use
this: Don't forget the <space> <underscore> after MatchCase:=False, )

cheers,
Matt.
 
M

Matt.

Hi Pikus!

I subbed your routine, and showed the Call statement too, for others who may
find it useful. I ran it in my spreadsheet, and it worked very well, but I
ended up using Chip's solution.

Thanks again,

cheers,

Matt.

Call SortSheet(Sheets("Accum"))

Sub SortSheet(ws As Worksheet)

Dim x As Integer
Dim alph As Variant
Dim a As Integer
Dim y As Integer
Dim z As Integer

For x = 2 To 50
alph = ws.Cells(x, 1).Value
a = x + 1
z = x
For y = a To 600
If ws.Cells(y, 1).Value < alph And ws.Cells(y, 1).Value <> "" Then
alph = ws.Cells(y, 1).Value
z = y
End If
Next y

If z <> x Then
ws.Rows(z).Cut
ws.Rows(x).Insert
End If

Next x

End Sub
 
P

pikus

I've personally been very happy with this method. If it is slowing yo
down I'd ask if you're using "Application.ScreenUpdating = False" o
not. If not, put that at the beginning of your code an
"Application.ScreenUpdating = True" at the end. It makes a WORLD o
difference. I've used it to sort hundreds of records and it's bee
quite fast, so unless you're sorting WAY more records than I am, I'd a
least recommend giving it a try. - Piku
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top