+ Reply to Thread
Results 1 to 9 of 9

Sort a row of strings, some containing blanks

  1. #1
    KobusD
    Guest

    Sort a row of strings, some containing blanks

    I am trying to sort a row, some cells contains strings and others
    blanks. Range = "B18" to "AY18"
    The "SORT" below does nothing! Can anyone see why?

    ---------
    Private Sub Workbook_Open()

    Dim OldRange As Range

    Set OldRange = Worksheets("Variables").Range("FA_budget_lines")
    OldRange.Sort 'Key1:=OldRange(1), Order1:=xlAscending, _ Header:=xlNo,
    Orientation:=xlSortColumns

    'some more code goes here...

    End Sub


  2. #2
    Bob Phillips
    Guest

    Re: Sort a row of strings, some containing blanks

    The Key argument is commented out, which looks a problem to me.

    Also it is in workbook_open. DO you realise that only runs when the workbook
    is opened, and have you put it in the ThisWorkbook code module?

    Is FA_budget_lines a defined name?

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "KobusD" <[email protected]> wrote in message
    news:[email protected]...
    > I am trying to sort a row, some cells contains strings and others
    > blanks. Range = "B18" to "AY18"
    > The "SORT" below does nothing! Can anyone see why?
    >
    > ---------
    > Private Sub Workbook_Open()
    >
    > Dim OldRange As Range
    >
    > Set OldRange = Worksheets("Variables").Range("FA_budget_lines")
    > OldRange.Sort 'Key1:=OldRange(1), Order1:=xlAscending, _ Header:=xlNo,
    > Orientation:=xlSortColumns
    >
    > 'some more code goes here...
    >
    > End Sub
    >




  3. #3
    KobusD
    Guest

    Re: Sort a row of strings, some containing blanks

    Thanks for the swift reply! The " ' " was left there by mistake when I
    posted.
    "Yes" to all other questions....

    For some reason it does not work when I run the macro (the same code is
    in another module as well - also with no success). The range does
    however sort if I go to Excel and do it manually.


  4. #4
    Jim Cone
    Guest

    Re: Sort a row of strings, some containing blanks

    K,

    Replace...
    Orientation:=xlSortColumns
    with...
    Orientation:=xlLeftToRight

    Jim Cone
    San Francisco, USA

    "KobusD" <[email protected]>
    wrote in message
    news:[email protected]
    I am trying to sort a row, some cells contains strings and others
    blanks. Range = "B18" to "AY18"
    The "SORT" below does nothing! Can anyone see why?
    ---------
    Private Sub Workbook_Open()
    Dim OldRange As Range
    Set OldRange = Worksheets("Variables").Range("FA_budget_lines")
    OldRange.Sort 'Key1:=OldRange(1), Order1:=xlAscending, _ Header:=xlNo,
    xlSortColumns
    'some more code goes here...
    End Sub


  5. #5
    KobusD
    Guest

    Re: Sort a row of strings, some containing blanks

    It works! Thanks!!!
    I'm curious to know why my setting did not work (is "SortColumns" only
    applicable to Pivot tables?)


  6. #6
    Jim Cone
    Guest

    Re: Sort a row of strings, some containing blanks

    K,
    For some reason known only to Microsoft and maybe not even them,
    using the Orientation constants requires you to be in a different universe...

    xlLeftToRight = 2
    xlTopToBottom = 1
    while...
    xlSortRows = 2
    xlSortColumns = 1

    Sorting columns, requires a constant with a value of 2.
    Jim Cone
    San Francisco, USA


    "KobusD" <[email protected]> wrote in message news:[email protected]...
    It works! Thanks!!!
    I'm curious to know why my setting did not work (is "SortColumns" only
    applicable to Pivot tables?)


  7. #7
    KobusD
    Guest

    Re: Sort a row of strings, some containing blanks

    Thanks, Jim.


  8. #8
    Bruno Campanini
    Guest

    Re: Sort a row of strings, some containing blanks

    "Jim Cone" <[email protected]> wrote in message
    news:%[email protected]...
    > K,
    > For some reason known only to Microsoft and maybe not even them,
    > using the Orientation constants requires you to be in a different
    > universe...
    >
    > xlLeftToRight = 2
    > xlTopToBottom = 1
    > while...
    > xlSortRows = 2
    > xlSortColumns = 1
    >
    > Sorting columns, requires a constant with a value of 2.


    What's wrong Jim?
    KobusD wanted to sort on [B18:AY18] which is a row
    not a column, then Orientation:=xlSortRows.
    Why then xlSortRows and not xlSortRow?
    I guess because xlSortRows is also applicable to matrices,
    not only vectors.

    "For some reason known only to Microsoft and maybe not even them, "
    I agree with you many times it happens to be true.
    But I think this is not the case.

    Regards
    Bruno



  9. #9
    Jim Cone
    Guest

    Re: Sort a row of strings, some containing blanks

    Bruno,
    We are looking at different ends of the elephant.
    When KobusD gets done, the columns will be in different positions.
    I call that sorting columns.

    Regards,
    Jim Cone
    San Francisco, USA,


    "Bruno Campanini"
    <[email protected]>
    wrote in message
    news:[email protected]...

    "Jim Cone" <[email protected]> wrote in message
    news:%[email protected]...
    > K,
    > For some reason known only to Microsoft and maybe not even them,
    > using the Orientation constants requires you to be in a different
    > universe...
    >
    > xlLeftToRight = 2
    > xlTopToBottom = 1
    > while...
    > xlSortRows = 2
    > xlSortColumns = 1
    >
    > Sorting columns, requires a constant with a value of 2.


    What's wrong Jim?
    KobusD wanted to sort on [B18:AY18] which is a row
    not a column, then Orientation:=xlSortRows.
    Why then xlSortRows and not xlSortRow?
    I guess because xlSortRows is also applicable to matrices,
    not only vectors.

    "For some reason known only to Microsoft and maybe not even them, "
    I agree with you many times it happens to be true.
    But I think this is not the case.

    Regards
    Bruno



+ 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