Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
I am trying to sort a row, some cells contains strings and others
blanks. Range = "B18" to "AY18" The "SORT" below does nothing! Can anyone see why? --------- Private Sub Workbook_Open() Dim OldRange As Range Set OldRange = Worksheets("Variables").Range("FA_budget_lines") OldRange.Sort 'Key1:=OldRange(1), Order1:=xlAscending, _ Header:=xlNo, Orientation:=xlSortColumns 'some more code goes here... End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
The Key argument is commented out, which looks a problem to me.
Also it is in workbook_open. DO you realise that only runs when the workbook is opened, and have you put it in the ThisWorkbook code module? Is FA_budget_lines a defined name? -- HTH RP (remove nothere from the email address if mailing direct) "KobusD" wrote in message ups.com... I am trying to sort a row, some cells contains strings and others blanks. Range = "B18" to "AY18" The "SORT" below does nothing! Can anyone see why? --------- Private Sub Workbook_Open() Dim OldRange As Range Set OldRange = Worksheets("Variables").Range("FA_budget_lines") OldRange.Sort 'Key1:=OldRange(1), Order1:=xlAscending, _ Header:=xlNo, Orientation:=xlSortColumns 'some more code goes here... End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
Thanks for the swift reply! The " ' " was left there by mistake when I
posted. "Yes" to all other questions.... For some reason it does not work when I run the macro (the same code is in another module as well - also with no success). The range does however sort if I go to Excel and do it manually. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
K,
Replace... Orientation:=xlSortColumns with... Orientation:=xlLeftToRight Jim Cone San Francisco, USA "KobusD" wrote in message ups.com I am trying to sort a row, some cells contains strings and others blanks. Range = "B18" to "AY18" The "SORT" below does nothing! Can anyone see why? --------- Private Sub Workbook_Open() Dim OldRange As Range Set OldRange = Worksheets("Variables").Range("FA_budget_lines") OldRange.Sort 'Key1:=OldRange(1), Order1:=xlAscending, _ Header:=xlNo, xlSortColumns 'some more code goes here... End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
It works! Thanks!!!
I'm curious to know why my setting did not work (is "SortColumns" only applicable to Pivot tables?) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
K,
For some reason known only to Microsoft and maybe not even them, using the Orientation constants requires you to be in a different universe... xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 Sorting columns, requires a constant with a value of 2. Jim Cone San Francisco, USA "KobusD" wrote in message oups.com... It works! Thanks!!! I'm curious to know why my setting did not work (is "SortColumns" only applicable to Pivot tables?) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
Thanks, Jim.
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
"Jim Cone" wrote in message
... K, For some reason known only to Microsoft and maybe not even them, using the Orientation constants requires you to be in a different universe... xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 Sorting columns, requires a constant with a value of 2. What's wrong Jim? KobusD wanted to sort on [B18:AY18] which is a row not a column, then Orientation:=xlSortRows. Why then xlSortRows and not xlSortRow? I guess because xlSortRows is also applicable to matrices, not only vectors. "For some reason known only to Microsoft and maybe not even them, " I agree with you many times it happens to be true. But I think this is not the case. Regards Bruno |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort a row of strings, some containing blanks
Bruno,
We are looking at different ends of the elephant. When KobusD gets done, the columns will be in different positions. I call that sorting columns. Regards, Jim Cone San Francisco, USA, "Bruno Campanini" wrote in message ... "Jim Cone" wrote in message ... K, For some reason known only to Microsoft and maybe not even them, using the Orientation constants requires you to be in a different universe... xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 Sorting columns, requires a constant with a value of 2. What's wrong Jim? KobusD wanted to sort on [B18:AY18] which is a row not a column, then Orientation:=xlSortRows. Why then xlSortRows and not xlSortRow? I guess because xlSortRows is also applicable to matrices, not only vectors. "For some reason known only to Microsoft and maybe not even them, " I agree with you many times it happens to be true. But I think this is not the case. Regards Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Formula to sort two strings | Excel Worksheet Functions | |||
HOW DO YOU SORT EXCEL FILE WITH BLANKS | Excel Worksheet Functions | |||
How do I sort a list that contains blanks that I want to keep? | Excel Discussion (Misc queries) | |||
Can I remove blanks from a range without using sort? | Excel Discussion (Misc queries) | |||
how do I reset the default sort order in excel xp back to blanks . | Excel Discussion (Misc queries) |