Sorting Data Using Double Click on Header but multiple range

[amartakale]

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

[amartakale]

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

[amartakale]

Dear Paul sir

Can you look in this issue,Pls?

Regards
Amar

[Paul_Hossler]

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

[amartakale]

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.

[Paul_Hossler]

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

[amartakale]

Stocks Sorting_Amar.xlsm

[amartakale]

Hi paul sir I hope you understand above my query.

[Paul_Hossler]

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

[amartakale]

Stocks Sorting_Amar sample.xlsm

[amartakale]

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.

[amartakale]

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

[Paul_Hossler]

Stocks Sorting_Amar sample.xlsm

This does not seem to have anything to do with the original workbook

[Paul_Hossler]

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

[amartakale]

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

[Paul_Hossler]

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

[amartakale]

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

[Paul_Hossler]

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

[amartakale]

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

[Paul_Hossler]

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