Excel 2000 VBA Sort without Select

Discussion in 'Microsoft Excel Programming' started by Matt., Jan 13, 2004.

  1. Matt.

    Matt. Guest

    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.
     
    Matt., Jan 13, 2004
    #1
    1. Advertisements

  2. Matt.

    Matt. Guest

    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
    news:egXMb.10040$...
    > 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.
    >
    >
     
    Matt., Jan 13, 2004
    #2
    1. Advertisements

  3. Matt.

    pikus Guest

    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
     
    pikus, Jan 13, 2004
    #3
  4. Matt.

    Chip Pearson Guest

    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
    news:4EXMb.10077$...
    > 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
    > news:egXMb.10040$...
    > > 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.
    > >
    > >

    >
    >
     
    Chip Pearson, Jan 13, 2004
    #4
  5. Matt.

    Matt. Guest

    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
    news:p...
    > 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/
    >
     
    Matt., Jan 13, 2004
    #5
  6. Matt.

    Matt. Guest

    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
    news:...
    > 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
    > news:4EXMb.10077$...
    > > 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
    > > news:egXMb.10040$...
    > > > 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.
    > > >
    > > >

    > >
    > >

    >
    >
     
    Matt., Jan 13, 2004
    #6
  7. Matt.

    Matt. Guest

    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
    news:p...
    > 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/
    >
     
    Matt., Jan 13, 2004
    #7
  8. Matt.

    pikus Guest

    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
     
    pikus, Jan 13, 2004
    #8
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt.

    Excel 2000 Find without select VBA

    Matt., Jan 5, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    426
    Matt.
    Jan 5, 2004
  2. GoFigure

    Why Error Message "End Select without Select Case"?

    GoFigure, Dec 8, 2005, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    687
    Rowan Drummond
    Dec 9, 2005
  3. WhytheQ

    Sort method possible without using select

    WhytheQ, May 18, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    132
    Guest
    Aug 11, 2006
  4. Guest
    Replies:
    12
    Views:
    512
    Tushar Mehta
    Nov 17, 2006
  5. Bishop

    Refined Sort - Sort within a Sort

    Bishop, Apr 7, 2009, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    344
    Bishop
    Apr 7, 2009
Loading...

Share This Page