View Full Version : Sorting Data Using Double Click on Header but multiple range
amartakale
02-27-2019, 01:54 AM
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
03-05-2019, 10:07 PM
any one help me on this issue (Sorting Data Using Double Click on Header but multiple range)?
amartakale
03-07-2019, 09:28 PM
Dear Paul sir
Can you look in this issue,Pls?
Regards
Amar
Paul_Hossler
03-08-2019, 03:23 PM
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
03-10-2019, 10:03 PM
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
03-12-2019, 08:43 AM
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
03-13-2019, 03:30 AM
23891
amartakale
03-13-2019, 03:30 AM
Hi paul sir I hope you understand above my query.
Paul_Hossler
03-13-2019, 08:35 AM
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
03-14-2019, 01:09 AM
23900
amartakale
03-14-2019, 01:17 AM
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
03-14-2019, 03:29 AM
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
03-14-2019, 06:35 AM
23900
This does not seem to have anything to do with the original workbook
Paul_Hossler
03-14-2019, 06:53 AM
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
03-14-2019, 10:23 PM
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
03-15-2019, 06:09 AM
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
03-17-2019, 10:06 PM
the attachment in post #14 above .I attached sheet again here23919
Paul_Hossler
03-18-2019, 06:48 AM
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
03-19-2019, 12:19 AM
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
03-19-2019, 05:19 AM
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
03-19-2019, 09:11 PM
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
03-20-2019, 04:49 AM
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
03-22-2019, 02:50 AM
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
03-22-2019, 07:13 AM
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
03-24-2019, 09:31 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.