Sorting Individual Rows Horizontally

[Jon Ballard]

I have a list of instruments that I'm trying to sort in alphabetical order
from left to right. The list is part of a larger database, where each row
pertains to a specific song. I'm trying to get my list of instruments to be
in alphabetical order on each line individually. I have about 40 columns of
different instruments, and 50,000 rows, each record is unique, but some
contain similar data. So what I have right now might look something like
this:

Acoustic Guitar Strings Electric Guitar Bass Percussion

and I want it to look like this:

Acoustic Guitar Bass Electric Guitar Percussion Strings

I'm just trying to find a formula, or VB script that I can run to do this
automatically. I'm not very experienced with VB, so I'm not even sure if
this is possible, but I've exhausted google, and have come up with nothing
that works. Any help is very very much appreciated!!!

 

[BobT]

Here you go:

1. From within Excel, press ALT+F11. This will launch the VBA tool.
2. Choose INSERT | MODULE. This will open a new module for you.
3. Paste the code below.
4. Switch back to your spreadsheet.
5. Select the first (leftmost, uppermost) cell.
6. Run the macro by choosing the TOOLS | MACRO | RUN and run the SortMe
macro.

The status bar will show what row it is currently working on. To speed
things up, I've added the "application.screenupdating" lines. The FALSE line
stops the screen updating - so you won't see moving down through the 50,000
rows. Trust me, this will run faster than if you delete that command. But
if you want, you can run with it off, but it will take more than twice as
long to run (I'm betting it'll take several minutes to complete).


Sub SortMe()

'Select the first cell (leftmost) on your first row.
'Make sure all rows have a value in the first cell.
'Make sure there are no empty cells within the row.

Application.ScreenUpdating = False

While ActiveCell.Value <> ""

While ActiveCell.Offset(0, 1).Value <> ""

Application.StatusBar = "Now on row " & ActiveCell.Row

x = 1

BaseCell = ActiveCell.Address

While ActiveCell.Offset(0, x).Value <> ""

If (Range(BaseCell).Value > ActiveCell.Offset(0, x).Value)
Then
'Swap
Temp = Range(BaseCell).Value
Range(BaseCell).Value = ActiveCell.Offset(0, x).Value
ActiveCell.Offset(0, x).Value = Temp
End If
x = x + 1

Wend

ActiveCell.Offset(0, 1).Select

Wend

'move to next row
ActiveCell.Offset(1, 0).Select
Selection.End(xlToLeft).Select

Wend

Application.ScreenUpdating = True
Application.StatusBar = False

End Sub

 

[Jon Ballard]

Wow thank you so much, I'm having some problems with it though, probably just
something I'm doing wrong on my own end. But I'm getting some errors when I
try and run the Macro, One of them says "Compile Error, Syntax Error" and
highlights this part:

I