Results 1 to 7 of 7
  1. #1
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: peraphs impossible... order line base color... (2000)

    Check out MS MVP Chip Pearson's code to SortByColor

    Steve

  2. #2
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: peraphs impossible... order line base color... (2000)

    ok... but how to start function and how to set the range of sheet?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: peraphs impossible... order line base color... (2000)

    To "start" the function, As chip indicates, you would place the formula into it into an empty column in that row and copy it down to the end of the column. This can be done manually or via code. You now have a column with the "color value". The sort will use this intermediate column (the column can be deleted after the sort is done).

    I don't understand what you mean by setting the "range of sheet". Could you elaborate?

    Steve

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: peraphs impossible... order line base color... (2000)

    the "range of sheet"...
    A5:AF147

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: peraphs impossible... order line base color... (2000)

    Presuming you know that you start in A5 and you know that AF is the final column, how about this (it presumes that AF will be filled to the bottom of the range):

    <pre>Dim rng As Range
    With Worksheets("STAT_NEW_1")
    Set rng = .Range(.Range("A5"), _
    .Cells(.Cells.Rows.Count, "AF").End(xlUp))
    End With</pre>


    Steve

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: peraphs impossible... order line base color... (2000)

    Resolved , tks.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    peraphs impossible... order line base color... (2000)

    Is possible to order line in sheet based 2 key, color in column AF and value in column AE????
    i use in standard mode:

    Sub ORDINA_REGIONI()

    Dim WS As Worksheet
    Dim ULTIMA As Long
    Dim MIO_RANGE As RANGE

    Set WS = Worksheets("STAT_NEW_1")

    With WS

    .Unprotect PASSWORD:="SAL21"

    ULTIMA = Sheets("STAT_NEW_1").RANGE("AF" & Rows.Count).End(xlUp).Row
    Set MIO_RANGE = Sheets("STAT_NEW_1").RANGE("A5:AF" & ULTIMA)
    MIO_RANGE.Sort Key1:=RANGE("AF5"), Order1:=xlAscending, Key2:=RANGE("A5") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom

    .Protect PASSWORD:="SAL21"

    End With

    End Sub

Posting Permissions

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