Sorting a value based upon the values that appear in row A

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

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
 
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
 
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.
Click to expand...

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
 
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).
Click to expand...

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.
 
Norman

Am i right in saying though this will not sort
irrespective of the + or - ?

Steve
 
Hi Steve,
Am i right in saying though this will not sort
irrespective of the + or - ?
Click to expand...

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.
 
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 said:
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
Click to expand...
 
Back
Top