Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 25

Thread: Sorting Data Using Double Click on Header but multiple range

  1. #1

    Sorting Data Using Double Click on Header but multiple range

    Dear Sir

    Can we Sorting Data Using Double Click on Header but multiple range. In attachment I have only one sector (Bank) sort on double click But I want also sort sector Auto & Auto Ancillaries same as bank sector.It is possible.Pls help me.

    Regards'
    Amar
    Attached Files Attached Files

  2. #2
    any one help me on this issue (Sorting Data Using Double Click on Header but multiple range)?

  3. #3
    Dear Paul sir

    Can you look in this issue,Pls?

    Regards
    Amar

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    OK, try this

    There's no real error checking to fail softly (like mis-spelling one of the categories), but checks can be added

    Option Explicit
    
    Sub SortBlocks()
        Dim rCategory As Range
            
        Application.ScreenUpdating = False
        
        With ActiveSheet
            Set rCategory = .Columns(2)
            Call SortBlock(rCategory, "Auto Ancillaries")
            Call SortBlock(rCategory, "Auto")
            Call SortBlock(rCategory, "Banks")
        End With
        Application.ScreenUpdating = True
        MsgBox "Sorted"
    End Sub
    
    Private Sub SortBlock(r As Range, s As String)
        Dim rLastColumn As Range, rSort As Range, rStart As Range, rEnd As Range
        
        With r
            Set rStart = .Find(What:=s, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlWhole).Offset(1, 0)
            Set rEnd = rStart.End(xlDown)
            Set rLastColumn = .Parent.Cells(6, .Parent.Columns.Count).End(xlToLeft).EntireColumn
            Set rSort = Range(rStart, Intersect(rEnd.EntireRow, rLastColumn))
        End With
        
        With r.Parent.Sort
            .SortFields.Clear
            .SortFields.Add Key:=rSort.Columns(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange rSort
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Sir,It only work on Banks sector (Row 34).Not work on Auto (Row 22) & Auto Ancillaries (Row 7) for ascending & descending order,then what I do now.

  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    Quote Originally Posted by amartakale View Post
    Sir,It only work on Banks sector (Row 34).Not work on Auto (Row 22) & Auto Ancillaries (Row 7) for ascending & descending order,then what I do now.
    The macro sorts the three blocks A-Z by Company Name

    I did not tie the macro into any double click events

    What VERY SPECIFICALLY did you want, since I'm must have misunderstood

    For example,

    1. Double Click J6 and sort just "Auto Ancillaries" by column J High to Low, then Double click J6 again and sort just "Auto Ancillaries" by column J Low to High?

    or

    2. Double Click J6 and sort all 3 block3 by column J High to Low within each block, then Double click J6 again and sort all 3 blocks by their column J Low to High?


    or

    3. Double Click any Row 6 column header and sort all 3 blocks by that column High to Low within each block, then Double click J6 again and sort all 3 blocks by their column J Low to High?


    or

    4. Somethng else?


    You mentioned column header and Row


    Before

    Before.JPG

    After sorting by Company Name
    After.JPG
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7

  8. #8
    Hi paul sir I hope you understand above my query.

  9. #9
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    Quote Originally Posted by amartakale View Post
    Hi paul sir I hope you understand above my query.
    Again, it was not as specific as I hoped for

    I think I have it though

    Capture.JPG

    I did not like all the buttons (too much work for me) so I just used a double click in a cell marked with the Up/Down arrows

    A second double click will reverse the sort order
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10

  11. #11
    Thanks for effort pual sir,Exactly I want you done it but I want sorting for all column in data range/each block.

    Pls see above sheet I want Exactly that way sort data for all column header by double click. but I want data sort each block/Range.I future if add sector/block then I will change range of each sector & header of column/row number thats it.

  12. #12
    Here I attached another excel sheet for reference your (Sorting Data Using Double Click on Header).

    But it also sorting whole all column in data range & I want each block/range sorting as asc & des order.sort_table_by_double_clicking.xls

  13. #13
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    Quote Originally Posted by amartakale View Post
    This does not seem to have anything to do with the original workbook
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    Quote Originally Posted by amartakale View Post
    Here I attached another excel sheet for reference your (Sorting Data Using Double Click on Header).

    But it also sorting whole all column in data range & I want each block/range sorting as asc & des order.sort_table_by_double_clicking.xls
    I know how to sort by double clicking on a header

    Going by your other example it seems you wanted to sort by these columns the data in just the block (Auto, etc.) on a block by block basis

    Capture.JPG


    Are you now saying that

    if you double click J7, then

    you'd like rows 9:45 sorted by J9:J45 AND
    rows 49:50 sorted by J49:J50 AND
    rows 55:78 sorted by J55:J78

    ALL AT ONCE?????


    If you double click any of the blue cells in row7, then in the newerest attachment all the data groups will be sorted

    If that's NOT what you want, then I really have no idea, and will need a VERY SPECIFIC EXAMPLE, not just the same words repeated or workbooks that do not seem to have anything to do with the first request
    Attached Files Attached Files
    Last edited by Paul_Hossler; 03-14-2019 at 07:08 AM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    Sorry for confusion In above latest sheet I explain detailed as which I want output

    if I double click row8 (Range B8:AE8)
    Then Data sort as
    Rows sorted by B9:AE45 (Asc & Des order)


    if I double click row48 (Range B49:AE50)
    Then Data sort as
    Rows sorted by B49:AE50 (Asc & Des order)


    if I double click row54 (Range B55:AE78)
    Then Data sort as
    Rows sorted by B55:AE78 (Asc & Des order)


    Data sort each block / range not at one all sorting.




    If Sorting at once all block/range in each sector then it is amazing for me but it is another Query/ideas.first I required as separate sorting if we success then next stage I will thinking of your great ideas sir & if will sort done then I will salute Sir

  16. #16
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    if I double click row8 (Range B8:AE8)
    Then Data sort as
    Rows sorted by B9:AE45 (Asc & Des order)



    if I double click row48 (Range B49:AE50)
    Then Data sort as
    Rows sorted by B49:AE50 (Asc & Des order)



    if I double click row54 (Range B55:AE78)
    Then Data sort as
    Rows sorted by B55:AE78 (Asc & Des order)

    Isn't that what the attachment in post #9 above does?


    If you double click J8, then B9:AE45 are sorted ascending by J9:J45 and J9:J45 are shaded red

    Double click J8 again and then B9:AE45 are sorted descending
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  17. #17
    the attachment in post #14 above .I attached sheet again hereStocks Sorting.xlsm

  18. #18
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    I'm sorry, but attaching the same workbook again and repeating the same words again does not help

    Look at the attachment in post #9 and TELL ME SPECIFICALLY IN WORDS where it is not what you want

    It would help if you could manually create the final result
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #19
    Dear Sir,

    I put VBA code in Screener sheet in VBA project not insert model bcos in not work in model (I want to know why not working in model?).Now it work in Auto Ancillaries sector range (B9:AE45).by Double Click on Row 8 (B8 to AE8) any cell then data sort asc & des order,also highlight that range. You can also try.

    I want same this activity sorting in

    Auto sector range (B49:AE50) by double clicking Row 48 (B48 to AE48) &
    Bank sector range (B55:AE78) by double clicking Row 54 (B54 to AE54)Stocks Sorting_3.xlsm

    Sir,I hope you understand my issue.I attached sheet.


    Regards
    Amar

  20. #20
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,391
    Location
    I did the macro so that it used just the 3 header rows and figured out just where each of the data ranges were


    It looks like myData is fixed and double clicking anywhere in the column will sort

    
        ' Exit sub if double click outside of defined table range name "myDataTable"
        If Application.Intersect(ActiveCell, Range("myData").Cells) Is Nothing Then Exit Sub
    
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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