Add Custom Sort List To VBA Code
Mar 12, 2009
I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.
The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.
Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner? Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.
View 2 Replies
ADVERTISEMENT
Aug 3, 2009
I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, or D1. If the user "double clicks" on cell D1, I want to sort by column D but I want to apply a custom list. The data is in the "Custom Lists" table but I can't figure out how to apply to my code.
The sequence/sort order of the list is as follows:
aaa+, aaa, aaa-, aa+, aa, aa-, a+, a, a-, bbb+, bbb, bbb-, bb+, bb, bb-, b+, b, b-, ccc+, ccc, ccc-, cc+, cc, cc-, c+, c, c-, ddd+, ddd, ddd-, dd+, dd, dd-, d+, d, d-
code is as follows:
View 3 Replies
View Related
Aug 25, 2013
I am trying to create a custom sort list by going to preferences/custom lists and typing 37 items into a new list in Excel. It will not take all of the items after I type them in. Is there a limit as to how many items can be in the list. It seems to only take 18-20 items out of the 37 and erases the rest.
View 9 Replies
View Related
May 28, 2014
I am having trouble finding info on this. I am trying to create a custom list to sort data.
I only want to sort two of the letters in my first Level or Column. For instance I want to sort Column A Alphabeticaly as A, B, C, D, but once I get to E, have the rest of the list in a random order that is sorted by my next level. Is there a wildcard character that can be used to have my next sorting level take over once I get to E?
View 4 Replies
View Related
Apr 30, 2009
I have a large list of parcels to be delivered which have names, the last 2 letters of the postcodes, addresses tel. no. etc. I have created a custom sort list of postcodes in the order I want to deliver them. I can only list a total of 85 postcodes since there are 2 characters in each postcode and a return at the end of each line, hence 85*3=255.
Is there any way I can make excel sort my list of parcels using a spreadsheet list of the 2 postcode letters of unlimited length? There is a theoretical maximum of 676 postcodes in my area (26*26) so I would need it to be at least this long. It would also be more convenient if it were an excel based list since the editing of such a list would be more convenient.
View 3 Replies
View Related
Jan 23, 2009
I have a fairly large watch collection as below which I wear on a daily rotating basis(!).
FORTIS
Seiko
OMEGA SPEEDMASTER
Seiko
SINN 144
Seiko
Accurist
Seiko
TEMPTION
Seiko
OMEGA TRIPLE DATE
Seiko
Junkers....................
View 9 Replies
View Related
Apr 21, 2014
How to sort a column of data based on a custom list with more than 255 characters.
I have created a named range with 40 entries and then added data validation in the cells of column D using the above named range. However, I want to be able to sort column D in the same order as the named range but the custom data sort lists are restricted to 255 characters.
View 1 Replies
View Related
Feb 17, 2014
Sort Example.xlsx
I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.
A8 = Customer
B8 = Load Time
C8 = Delivery Time
Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"
I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).
The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.
The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM
Example is attached (desired sort is reflected).
View 14 Replies
View Related
Oct 8, 2006
Is it possible to make excel sort ascending or descending but from mid way through alphabet and then loop through the alphabet again. for example. If Cell A1 had "A" in, it would sort as normal.
Cell A1 = A
Cell A2 = B
Cell A3 = C
Cell A4 = D
Cell A5 = E
If Cell A1 had "C" in, it would sort from "C" through the alphabet and then loop to the start of the alphabet as shown below.
Cell A1 = C
Cell A2 = D
Cell A3 = E
Cell A4 = A
Cell A5 = B
View 4 Replies
View Related
May 30, 2012
As of right now these are the steps i do to sort...i click custom sort choose My data has headers and then i select from the drop down list the word FRNAME.
is there any way i can setup a macro to do this for me? i tried recording the macro but it just is recording me choosing the column FRNAME is in. This does not work for me since FRNAME end up being in different columns all the time but will always be in row 1.
View 1 Replies
View Related
May 20, 2014
I am trying to sort a sheet based on the values in a column.
S, SI, M, MI. and it need to be sorted only in this order.
View 2 Replies
View Related
Aug 18, 2006
I would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I don’t know why its not working:
Sub CustSort()
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Application.AddCustomList Array(MyValue)
ActiveSheet.UsedRange.Sort _
Key1:= Range("A1"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=MyCount, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.DeleteCustomList MyCount
End Sub
find attached an example, try entering 300000 in the inputbox the custom sort doesn’t work
View 6 Replies
View Related
Sep 28, 2006
I'm wanting to be able to sort a table of data using a custom list for both the first criteria and the second criteria. I've setup my custom list correctly and it works perfectly for the first criteria, however, with the second criteria Excel just adopts its default sort order.
Is it possible to get it to use the custom list on the second criteria as well? Is there a simple VBA solution to this?
View 4 Replies
View Related
Feb 13, 2007
I feel I've searched thoroughly and wasn't able to find the right answer. I've attached a daily report example that needs the "Product" field to be sorted in a particular order. The order should be JEX, Q3791J, YOO5, KLX9, GHT (all similar products need to be grouped together).
Since the report is run daily the number of rows changes daily: one day can have 50 entries and the next 10. Is there a way to dictate the sort order and insert a blank row between each product grouping?
Once the Products have been separated into their groups, how would I sort each grouping by the "Term" column?
View 9 Replies
View Related
Aug 4, 2009
This is somewhat related to a post I had submitted previously but a bit more complex http://www.excelforum.com/showthread.php?p=2139259. I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, D1, or E1.
If the user "double clicks" on cell D1, I want to sort by column D using following custom list: AA1, AA2, AA3, A1, A2, A3, ZZ1, ZZ2, ZZ3, Z1, Z2, Z3. If the user "double clicks" on cell E1, I want to sort by column E using following custom list: AAA+, AAA, AAA-, AA+, AA, AA-, A+, A, A-, ZZZ+, ZZZ, ZZ+, ZZ-, Z+, Z, Z-
The issue I seem to be encountering is that I can’t use more than one custom list (e.g., if I use the column D sort list for column D, column E is also using the same list). I can’t seem to apply individual lists to each column. Would I need to “hardcode” the lists in my macro?
View 5 Replies
View Related
Nov 23, 2011
I have found many macros for sorting multiple sheets by a single column in Excel, however, need macro for sorting multiple sheets by column "A", then column "B".
View 1 Replies
View Related
May 13, 2009
I am trying to create custom sort list. It works below when I define range as A1:A79.
Sub SortWS2()
Dim SortOrder As Variant
Dim sheetsorder As Range
Dim Ndx As Long
Application. ScreenUpdating = False
With Worksheets("Sort Order").Range("A1:A79")
For Ndx = .Cells.Count To 1 Step -1
Worksheets(.Cells(Ndx).Value).Move before:=Worksheets(1)
Next Ndx
End With
Application.ScreenUpdating = True
End Sub
I have created a dynamic range called sheetsorder. If I revise my code it does not work.
Sub SortWS2()................
View 6 Replies
View Related
May 21, 2008
can anyone modify below macro to sort this data:
before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc
after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc
Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub
View 9 Replies
View Related
May 23, 2008
I would like to sort my column A by "Urgent, High, Medium and Low". However, when I click on sort, it obviously sorts alphbetically. Need to code to sort all colums A:K.
View 5 Replies
View Related
May 3, 2014
Everyday I've to make Pivot Table from Data given by Branch Manager. When I create Pivot, my Row Label comes in order like
ICV TRUCK
LCV BUS
LCV TRUCK
MCV BUS
MCV TRUCK
PICKUP &
SCV shown in "automaticPivot" sheet in attached file. Later I arrange it in order of
MCV BUS
LCV BUS
MCV TRUCK
ICV TRUCK
LCV TRUCK
PICKUP
SCV by dragging manually everyday shown in "desiredRowLabelSort" sheet.
Is there any way that sort Row Label automatically instead of manually OR any other faster way than doing manually?
View 6 Replies
View Related
Jul 10, 2014
Is it possible to do a custom sort on a column so that I can change the order the way the months are sorted. The order I'm looking for is:
October
November
December
January
February
March
April
May
June
July
August
September
Also the format for the cells in that column is 01-Jan, I don't want to see the year because I enter and sort the date as if the year doesn't matter so it automatically registers as 2014, if that makes sense. Basically I want that order of the months regardless of the year.
View 5 Replies
View Related
Jul 10, 2012
I am trying to sort a bunch of data by a custom list from left to right and after running the macro successfully excel crashes upon saving. I have tried this on different computers, resaved, started from scratch and it always crashes so there must be something with the macro that is causing this.
here is my macro:
Edit: It seems that the last line of code is causing my problem. I have started from scratch and gone though all the code one at a time and saved after each run. everything is fine until the "Application.DeleteCustomList Application.CustomListCount" is run. after that excel crashes when saved.
VB:
Sub Macro3()
'
' Macro3 Macro
'
[Code]....
View 1 Replies
View Related
Jun 6, 2014
Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...
View 3 Replies
View Related
May 22, 2012
Basically, I have a sheet that shows time in Days going downwards (monday tuesday etc.) and is broken out by week. In Column C, I have Project Names. Columns G-O have numbers.
What I need to do is run a custom sort multiple times down the page so that each week has been sorted by Project Name (column C). I was hoping to be to just make a quick macro shortcut to run my custom sort and each new range I selected but I don't know how to make the macro run on the actively selected cells and not hard-coded cells.
View 2 Replies
View Related
May 7, 2013
I need to create a custom function that can do the following without modifying the selected range. If I have data in a row, I need a function that can give me the following result:
Original unsorted data:
79
45
23
56
1
The custom formula will not modify the selected range, and after saving it or copying somewhere and sorting it, it will provide the following result: if N or count(range)=5 , and p=1, average(second and third smallest values of the range)=34; and if N=4 (lets say that we errase the number 1 data point in the range), average(third and fourth smallest values of the range)=67.5 So it is basically a sort and then getting the averages of specified row numbers depending on N or count(range) Here is what I have.
I'm missing how to create a variable with the sorted range and how to use an index to refer to the rows I want to average:
Function CSORT(r, p)
Application.Volatile
'rs or something similar that will contain the sorted range r
Dim rs As Range
'Sort rs (The following line one doesn't work, so I need one that does)
rs = Application.Range(r).Sort
Dim p As Integer
[code]....
View 1 Replies
View Related
Jun 5, 2013
what I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.
Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.
In case this isn't clear enough...
Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10
Once I filter/custom sort, I see rows 1,2,3,8,9,10
When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.
View 6 Replies
View Related
Jun 20, 2013
I already have a Macro button built that hides values in column A. The next step I am trying to perform is a custom sort on column B in this order "Backordered", "Sourced", Shipped", and "Received". Here is my code that I have so far but where to begin adding in code to make this button sort.
Sub Inbound()
ActiveSheet.Cells.EntireColumn.Hidden = False
ActiveSheet.Cells.EntireRow.Hidden = False
Application.ScreenUpdating = False
[Code] ........
Excel 2007/Windows 7
View 1 Replies
View Related
Jun 5, 2014
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
View 2 Replies
View Related
Mar 4, 2010
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
View 9 Replies
View Related
Dec 2, 2008
A spreadsheet that needs a new menu item in a custom menu does not show the custom menu code. Tools-->Customize-->Commands-->Rearrange Commands shows the menu and allows me to add a new menu item and macro.
After saving and exiting excel and going back into the spreadsheet, the new menu item is gone. How can I find the code to do this to keep the menu item? In the VBE, I looked in the object browser(F2) in all of the libraries and could not find the code. In the workbook_open and workbook_activate I can disable it with CommandBars("Worksheet Menu Bar").Controls("menu").Delete and it disappears.
View 14 Replies
View Related
