| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a value based upon the values that appear in row A
I have three columns in my spreadheet.
They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
Hi Steve,
Assume your data to be in Columns A, B and C and that you want to swotch the order of the entite columns. Try: Sub Tester() Columns("C:C").Cut Columns("A:A").Insert Shift:=xlToRight Columns("C:C").Cut Columns("B:B").Insert Shift:=xlToRight End Sub --- Regards, Norman "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
Hi Steve,
Assume your data to be in Columns A, B and C and that you want to swotch the order of the entite columns. Translating into English(!): Assume your data to be in Columns A, B and C and that you want to switch the order of the entire columns --- Regards, Norman "Norman Jones" wrote in message ... Hi Steve, Assume your data to be in Columns A, B and C and that you want to swotch the order of the entite columns. Try: Sub Tester() Columns("C:C").Cut Columns("A:A").Insert Shift:=xlToRight Columns("C:C").Cut Columns("B:B").Insert Shift:=xlToRight End Sub --- Regards, Norman "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
Hi Steve,
Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). The best solution for this is Excel's inbuilt sort, using the horizontal sort option. This will enable you to sort the three data columns into any column sequence you wish and is independent of the location of the data. If you need to perform this function in code, try performing the operation with the macro recorder turned on. --- Regards, Norman "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
Norman
Am i right in saying though this will not sort irrespective of the + or - ? Steve -----Original Message----- Hi Steve, Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). The best solution for this is Excel's inbuilt sort, using the horizontal sort option. This will enable you to sort the three data columns into any column sequence you wish and is independent of the location of the data. If you need to perform this function in code, try performing the operation with the macro recorder turned on. --- Regards, Norman "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
Hi Steve,
Am i right in saying though this will not sort irrespective of the + or - ? I do not know what this means. However, the horizontal (LeftToRight or RightToLeft) sort option will allow you to sort your three columns of data by the column headers. The sort can be ascending or descending, exactly as for a 'conventional' vertical sort. If this option appeals to you, you may wish to consider setting your column headers in a sort-friendly manner. --- Regards, Norman wrote in message ... Norman Am i right in saying though this will not sort irrespective of the + or - ? Steve -----Original Message----- Hi Steve, Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). The best solution for this is Excel's inbuilt sort, using the horizontal sort option. This will enable you to sort the three data columns into any column sequence you wish and is independent of the location of the data. If you need to perform this function in code, try performing the operation with the macro recorder turned on. --- Regards, Norman "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
Looks like he is mixing up his threads.
-- Regards, Tom Ogilvy "Norman Jones" wrote in message ... Hi Steve, Am i right in saying though this will not sort irrespective of the + or - ? I do not know what this means. However, the horizontal (LeftToRight or RightToLeft) sort option will allow you to sort your three columns of data by the column headers. The sort can be ascending or descending, exactly as for a 'conventional' vertical sort. If this option appeals to you, you may wish to consider setting your column headers in a sort-friendly manner. --- Regards, Norman wrote in message ... Norman Am i right in saying though this will not sort irrespective of the + or - ? Steve -----Original Message----- Hi Steve, Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). The best solution for this is Excel's inbuilt sort, using the horizontal sort option. This will enable you to sort the three data columns into any column sequence you wish and is independent of the location of the data. If you need to perform this function in code, try performing the operation with the macro recorder turned on. --- Regards, Norman "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting a value based upon the values that appear in row A
You asked this question a while back.
Cells(1,"IV") cells(1,i) the 1 means row 1. Sub AASetColumns() Dim rng As Range, sStr As String, i As Long Set rng = Cells(1, "IV").End(xlToLeft) i = rng.Column Do sStr = LCase(Cells(1, i).Value) If sStr < "first name" And _ sStr < "surname" And _ sStr < "score" Then Cells(1, i).EntireColumn.Delete Else Select Case sStr Case "first name" If i < 2 Then Cells(1, i).EntireColumn.Cut Columns(2).Insert i = i + 1 End If Case "surname" If i < 1 Then Cells(1, i).EntireColumn.Cut Columns(1).Insert i = i + 1 End If Case "score" If i < 3 Then Cells(1, i).EntireColumn.Cut Columns(3).Insert i = i + 1 End If End Select End If i = i - 1 Loop While i 0 End Sub -- Regards, Tom Ogilvy "Steve" wrote in message ... Thanks guys. Tom That is exactly what I am looking for. So that i can use it with other scenarios would you please explain which part of the code tells me to look in row 1 only ? One more thing...would it be possible to re-order the columns ? i.e. "surname","first name" and "score" (in that order) irrespective of the order they appear in the original spreadsheet ? Regards Steve -----Original Message----- Dim rng as Range, sStr as String, i as Long set rng = cells(1,"IV").End(xltoLeft) for i = rng.column to 1 step -1 sStr = lcase(cells(1,i).Value) if sStr < "first name" and _ sStr < "surname" and _ sStr < "score" then cells(1,i).EntireColumn.Delete end if Next -- Regards, Tom Ogilvy "Steve" wrote in message ... Hi I would like to delete columns in a spreadsheet dependant upon the value in row a. So for example the macro could say delete all columns other than those that have "First Name", "Surname" or "Score" in row a. Is this possible ? If so, can you tell me how to do it ? Thanks in advance. Steve . "Steve" wrote in message ... I have three columns in my spreadheet. They appear in the following order (these column names appear in row A): Score FirstName Surname Is it possible to write a macro that will re-order these columns so that they appear as follows: Surname FirstName Score Ideally, any solution will be based on the strings that appear on the above and not on the column letters (as it is possible that they may appear in a different order from time to time). Many thanks Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
| Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
| Sorting cell data based on values | Excel Discussion (Misc queries) | |||
| Search/Filter to find values in another range based on two cell values | Excel Programming | |||
| Predict Y-values on new X-values based on other actual X and Y values? | Excel Programming | |||
Hybrid Mode
