Thanks: 0
Needs Pictures: 0
Results 1 to 6 of 6
-
4th July 2020, 10:23 AM #1
Question on a piece of VB code for Excel sort
G'day
I have a sort that has a problem. It is attached to a macro button, and I make my selection or rows in the sheet, run the macro, sorting the rows by columns D and G (so G within D). It's actually two more columns than that, but never mind for this question.
When I do the sort manually and record it (macro) it adds this:
DataOption:=xlSortTextAsNumbers
to the answer sort numbers that look like numbers as numbers - it will not sort properly without it.
If I put this in my abbreviated code sort:
Selection.Sort.SortFields.Add2 Key1:=Range("D3"), SortOn:=xlSortOnValues
it gives
Unable to get the Sort property of the Range class
because it wants Range"A5:AG9" if I have selected rows 5-9.
If I put this in my abbreviated code sort:
Selection.Sort Key1:=Range("D3"), Key2:=Range("G3"), DataOption:=xlSortTextAsNumbers
it gives
Application-defined or object-defined error
because it doesn't like the ", DataOption:=xlSortTextAsNumbers" - it used to work (but incorrectly) until I added that phrase.
What it does like is the very long winded:
ActiveWorkbook.Worksheets("MUSIC").Sort.SortFields.Add2 Key:=Range( _
"D118:D746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("MUSIC").Sort.SortFields.Add2 Key:=Range( _
"G118:G746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("MUSIC").Sort.SortFields.Add2 Key:=Range( _
"F118:F746"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("MUSIC").Sort
.SetRange Range("A118:AC746")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
but I can't put specific ranges in the code - has to be Selection.
So the question is: how to get the phrase ", DataOption:=xlSortTextAsNumbers" into the Key?
-
4th July 2020 10:23 AM # ADSGoogle Adsense Advertisement
- Join Date
- Always
- Location
- Advertising world
- Posts
- Many
-
4th July 2020, 12:58 PM #2GOLD MEMBER
- Join Date
- Mar 2018
- Location
- Sydney
- Posts
- 1,166
I use tables to simplify this. Format your range as a table and operate on that. That way, you don't need to select the entire range, just a portion of the range (in your case it could be G118:G119 and F118:F119)
Here's a working example that I use, that only selects a subset of values in the table. It clears the current sort, adds a new sort, and then does the sort...
ActiveWorkbook.Worksheets("Customers").ListObjects("tblCustomers").Sort. _ SortFields.Clear
ActiveWorkbook.Worksheets("Customers").ListObjects("tblCustomers").Sort. _
SortFields.Add Key:=Range("D4: D5"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Customers").ListObjects("tblCustomers").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
-
4th July 2020, 07:53 PM #3
Thanks poundy - Apologies for the late response - been out all day since posting.
I've never used formal tables before, but I use Ranges as tables for vlookups etc and my standard is to call them Tbl.xxxx. I looked up how to create one and have put the "Format as Table" button on my quick access toolbar, but I'm sure I'm doing something wrong. It comes up with a whole bunch of colour schemes that I definitely don't want (already highly customised formatting).
So I clicked New Table Style and then gave it the name Tbl.UK_EU and clicked element style "Whole Table" (and didn't do any formatting). I have no idea what I'm doing there.
What I have is
ActiveWorkbook.Worksheets("Music").ListObjects("Tbl.UK_EU").Sort.SortFields.Clear
and I get "Subscript out of Range"
-
5th July 2020, 09:10 AM #4GOLD MEMBER
- Join Date
- Mar 2018
- Location
- Sydney
- Posts
- 1,166
Just looking at my code to see what's the difference.... can you go into name manager and confirm what it's range is ?
I just mocked up something as an example and then recorded a macro. So seems like it should work ? I removed the select and that also worked so the range is what i suspect...
Range("tbl.eu_uk[#All]").Select
ActiveWorkbook.Worksheets("Sheet1").ListObjects("tbl.eu_uk").Sort.SortFields. _
Clear
-
5th July 2020, 09:41 AM #5
I got to thinking a bit last evening, and solved it a slightly different way, but still using a Table range (although not strictly as a Table).
There are seven sections each with a dividing header, so I set up 7 ranges, and then called the sort and passed a parameter to it. It doesn't matter if the whole (1/7) range is sorted each time.
Once I had it working properly I then stripped out all the default sort info (as is my want) to make it easier to read.
Sub SORT_ColE_ColG()
If Not Application.Calculation = -4135 Then Application.Calculation = -4135
Application.ScreenUpdating = False: Application.EnableEvents = False
rw_save = Selection.Row
If rw_save > Range("au_nz").Row And rw_save < Range("uk_eu").Row Then: Tbl = "Tbl.AU_NZ": Call SORT_NON_Class(Tbl) 'to sort by by D,G,F,E
etc for the other 5 ranges, and then
If rw_save > Range("clss").Row And rw_save < Range("perf_only").Row Then Tbl = "Tbl.CLASS": Call SORT_CLASS(Tbl) 'to sort by D,E,F,G
ActiveWindow.ScrollRow = rw_save - 10 'to get me back to where I started
If Not Application.Calculation = -4105 Then Application.Calculation = -4105
Application.ScreenUpdating = True: Application.EnableEvents = True
End Sub
Sub SORT_NON_Class(Tbl)
Sheets("MUSIC").Sort.SortFields.Clear
Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("D3")
Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("G3"), DataOption:=xlSortTextAsNumbers
Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("F3"), DataOption:=xlSortTextAsNumbers
Sheets("MUSIC").Sort.SortFields.Add2 Key:=Range("E3")
With Sheets("MUSIC").Sort: .SetRange Range(Tbl): .Header = xlYes: .Apply: End With
End Sub
EDIT:
Now I can strip the sort back even further:
Sub SORT_NON_Class(Tbl)
With Sheets("MUSIC").Sort.SortFields
.Clear: .Add2 Key:=Range("D3"): .Add2 Key:=Range("G3"), DataOption:=xlSortTextAsNumbers
.Add2 Key:=Range("F3"), DataOption:=xlSortTextAsNumbers: .Add2 Key:=Range("E3")
End With
With Sheets("MUSIC").Sort: .SetRange Range(Tbl): .Header = xlYes: .Apply: End With
End Sub
So thanks for your help poundy - it tipped me in the right direction
-
5th July 2020, 09:46 AM #6
Similar Threads
-
Question on Conditional Formatting Excel 2016
By FenceFurniture in forum COMPUTERSReplies: 13Last Post: 13th September 2019, 08:47 PM -
Question: How to Sort/Filter Market Place/Auctions Pages ?
By RoyG in forum FORUMS INFO, HELP, DISCUSSION & FEEDBACKReplies: 0Last Post: 4th February 2016, 12:55 PM -
Excel Question
By Chris Parks in forum COMPUTERSReplies: 5Last Post: 30th July 2011, 12:03 AM -
Excel question
By Daddles in forum COMPUTERSReplies: 26Last Post: 6th June 2007, 12:29 AM -
hollowing tools question (and my latest piece)
By TimberNut in forum WOODTURNING - GENERALReplies: 3Last Post: 8th June 2005, 11:39 PM