PC Review


Reply
Thread Tools Rate Thread

Excel 2000 VBA Sort without Select

 
 
Matt.
Guest
Posts: n/a
 
      13th Jan 2004
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.


 
Reply With Quote
 
 
 
 
Matt.
Guest
Posts: n/a
 
      13th Jan 2004
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." <(E-Mail Removed)> wrote in message
news:egXMb.10040$(E-Mail Removed)...
> 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.
>
>



 
Reply With Quote
 
 
 
 
pikus
Guest
Posts: n/a
 
      13th Jan 2004
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

 
Reply With Quote
 
Chip Pearson
Guest
Posts: n/a
 
      13th Jan 2004
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." <(E-Mail Removed)> wrote in message
news:4EXMb.10077$(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
> news:egXMb.10040$(E-Mail Removed)...
> > 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.
> >
> >

>
>



 
Reply With Quote
 
Matt.
Guest
Posts: n/a
 
      13th Jan 2004
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 >" <<(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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/
>



 
Reply With Quote
 
Matt.
Guest
Posts: n/a
 
      13th Jan 2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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." <(E-Mail Removed)> wrote in message
> news:4EXMb.10077$(E-Mail Removed)...
> > 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." <(E-Mail Removed)> wrote in message
> > news:egXMb.10040$(E-Mail Removed)...
> > > 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.
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Matt.
Guest
Posts: n/a
 
      13th Jan 2004
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 >" <<(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> 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/
>



 
Reply With Quote
 
pikus
Guest
Posts: n/a
 
      13th Jan 2004
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Refined Sort - Sort within a Sort Bishop Microsoft Excel Programming 0 7th Apr 2009 09:00 PM
End Select without Select Case, Block If without End If errors =?Utf-8?B?QXRyZWlkZXM=?= Microsoft Excel Programming 12 17th Nov 2006 06:10 PM
VBA in Excel, Know a way to disable SORT without protectsheet? =?Utf-8?B?enVsZmVyNw==?= Microsoft Excel Misc 2 23rd Jun 2006 06:18 PM
Sort, and then sort from the first sort =?Utf-8?B?S0I=?= Microsoft Access 9 9th Jun 2006 04:07 PM
Excel 2000 Find without select VBA Matt. Microsoft Excel Programming 3 5th Jan 2004 05:56 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:02 PM.