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

23889




After sorting by Company Name
23890

[amartakale]

23891

[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

23894

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]

23900

[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.23901

[Paul_Hossler]

23900

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.23901

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

23902


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:clap::bow:

[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 here23919

[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)23925

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

[amartakale]

Good Morning Pual Sir,

Then what I do now? I have only basic knowledge of VBA. Can you put code in above my latest sheet (Stocks Sorting_3.xlsm (http://www.vbaexpress.com/forum/attachment.php?attachmentid=23925&d=1552979916)) then sorting data asc & des order in each block. currently there are more than 20 sector/block/ranges for sorting as asc & des order in each set.if you done above 3 sector for sorting then remaining I will do (only will ranges change of each block).

[Paul_Hossler]

23932

1. There as a space after the worksheet name "Screener " -- I fixed

2. If you double click the marker cells, the block of data will sort

[amartakale]

Lastly You done it Sir,exactly what I want?.

but small issue there are more than 500 rows.All block sort well & nice but between 500 rows there 2 block/set which is not asc & des order sort by double click,why I dont know but other 20 block/set properly asc & des order sort by double click.can you give me suggestion why not sorting.

Thanks again sir for your valuable effort for me.

[Paul_Hossler]

Because your data is very formatted, it is necessary that you make certain that you follow your current format for each category, so I suggest you check the structure of any data that does not sort correctly

23940

One category had an extra row that caused data to sort incorrectly

23939

I fixed that in the attachment

[amartakale]

Awesome and unbelievable, it was more than my expectation. I got my mistake & now sorting is very very correctly.

You are Great Paul Sir. Thank you very much again. I keep this work in mind for my future work,very useful sir:clap::clap::bow:

It's Great output for this Group & me also.