Excel 2000 VBA Sort without Select
 

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.

Excel 2000 VBA Sort without Select
 

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.


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

Excel 2000 VBA Sort without Select
 

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

--
Message posted from http://www.ExcelForum.com

Excel 2000 VBA Sort without Select
 

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


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


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

Excel 2000 VBA Sort without Select
 

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.

"pikus " wrote in message
...
Dim ws As Worksheet
See if this helps. I'll be glad to explain how it works if you need me
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 x


---
Message posted from http://www.ExcelForum.com/

Excel 2000 VBA Sort without Select
 

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.

"Chip Pearson" wrote in message
...
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


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


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

Excel 2000 VBA Sort without Select
 

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


"pikus " wrote in message
...
Dim ws As Worksheet
See if this helps. I'll be glad to explain how it works if you need me
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 x


---
Message posted from http://www.ExcelForum.com/

Excel 2000 VBA Sort without Select
 

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

--
Message posted from http://www.ExcelForum.com