+ Reply to Thread
Results 1 to 5 of 5

Sort by Alpha, ignore blanks

  1. #1
    ufo_pilot
    Guest

    Sort by Alpha, ignore blanks

    I have this code, but realize that the blanks are sorted to the top
    How can I prevent this. They need to stay at the bottom
    I need the blanks, to add rows of names in the future.
    After each new name is entered, it should rearrange the data in the cells
    specified in alphabetical order of the names.

    Sub Alpha()
    ActiveWindow.LargeScroll ToRight:=3
    Range("AS2:DS100").Select
    ActiveWindow.ScrollRow = 1
    Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveWindow.SmallScroll ToRight:=-3
    ActiveWindow.ScrollColumn = 1
    Range("C8").Select
    End Sub

    Thank you for any suggestions

  2. #2
    Tushar Mehta
    Guest

    Re: Sort by Alpha, ignore blanks

    Depending on how new information is entered, one possible solution
    would be to sort only the range containing data.

    Sub Alpha()
    Range(Range("AS2:DS2"), Range("AS2:DS2").End(xlDown)).Sort _
    Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub


    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <23189128-09E0-4F31-974E-53BE071F484E@microsoft.com>,
    ufopilot@discussions.microsoft.com says...
    > I have this code, but realize that the blanks are sorted to the top
    > How can I prevent this. They need to stay at the bottom
    > I need the blanks, to add rows of names in the future.
    > After each new name is entered, it should rearrange the data in the cells
    > specified in alphabetical order of the names.
    >
    > Sub Alpha()
    > ActiveWindow.LargeScroll ToRight:=3
    > Range("AS2:DS100").Select
    > ActiveWindow.ScrollRow = 1
    > Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _
    > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    > ActiveWindow.SmallScroll ToRight:=-3
    > ActiveWindow.ScrollColumn = 1
    > Range("C8").Select
    > End Sub
    >
    > Thank you for any suggestions
    >


  3. #3
    ufo_pilot
    Guest

    Re: Sort by Alpha, ignore blanks

    Thank you Tushar,
    changed the range from AS2:DS2 to AS2:AS100 and its working like a charm.

    "Tushar Mehta" wrote:

    > Depending on how new information is entered, one possible solution
    > would be to sort only the range containing data.
    >
    > Sub Alpha()
    > Range(Range("AS2:DS2"), Range("AS2:DS2").End(xlDown)).Sort _
    > Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _
    > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    > End Sub
    >
    >
    > --
    > Regards,
    >
    > Tushar Mehta
    > www.tushar-mehta.com
    > Excel, PowerPoint, and VBA add-ins, tutorials
    > Custom MS Office productivity solutions
    >
    > In article <23189128-09E0-4F31-974E-53BE071F484E@microsoft.com>,
    > ufopilot@discussions.microsoft.com says...
    > > I have this code, but realize that the blanks are sorted to the top
    > > How can I prevent this. They need to stay at the bottom
    > > I need the blanks, to add rows of names in the future.
    > > After each new name is entered, it should rearrange the data in the cells
    > > specified in alphabetical order of the names.
    > >
    > > Sub Alpha()
    > > ActiveWindow.LargeScroll ToRight:=3
    > > Range("AS2:DS100").Select
    > > ActiveWindow.ScrollRow = 1
    > > Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending, Header:=xlGuess, _
    > > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    > > ActiveWindow.SmallScroll ToRight:=-3
    > > ActiveWindow.ScrollColumn = 1
    > > Range("C8").Select
    > > End Sub
    > >
    > > Thank you for any suggestions
    > >

    >


  4. #4
    Howard Kaikow
    Guest

    Re: Sort by Alpha, ignore blanks

    "ufo_pilot" <ufopilot@discussions.microsoft.com> wrote in message
    news:23189128-09E0-4F31-974E-53BE071F484E@microsoft.com...
    > I have this code, but realize that the blanks are sorted to the top
    > How can I prevent this. They need to stay at the bottom
    > I need the blanks, to add rows of names in the future.
    > After each new name is entered, it should rearrange the data in the cells
    > specified in alphabetical order of the names.
    >
    > Sub Alpha()
    > ActiveWindow.LargeScroll ToRight:=3
    > Range("AS2:DS100").Select
    > ActiveWindow.ScrollRow = 1
    > Selection.Sort Key1:=Range("AS2"), Order1:=xlAscending,

    Header:=xlGuess, _
    > OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    > ActiveWindow.SmallScroll ToRight:=-3
    > ActiveWindow.ScrollColumn = 1
    > Range("C8").Select
    > End Sub
    >
    > Thank you for any suggestions


    Better would be to write your own sort code, the performance will be better.
    See http://www.standards.com/index.html?Sorting for a demo that shows how
    easy it is to improve on Excel's sort.
    Code is included for several sorting algorithms.



  5. #5
    Tushar Mehta
    Guest

    Re: Sort by Alpha, ignore blanks

    You are welcome. Glad to be of help.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <5CF7E765-16A4-427D-9479-D0B9612E0464@microsoft.com>,
    ufopilot@discussions.microsoft.com says...
    > Thank you Tushar,
    > changed the range from AS2:DS2 to AS2:AS100 and its working like a charm.
    >

    {snip}

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1