Sorting multiple rows

**ript73** · 09-06-2009, 01:49 AM

Hello all. Thanks in advance for any help you can give me. I have been trying to find a way to sort hundreds of rows and can't figure it out. Here is an example of what I have:

23 56 34 16 25 6
56 45 48 23 12 5
12 45 34 67 47 32
.
.
.
What I want to do is sort each row lowest to highest like so:

6 16 23 25 34 56
5 12 23 45 48 56
12 32 34 45 47 67

The sort by rows thing doesn't work.

Thanks again!

-Mark

**JBeaucaire** · 09-06-2009, 01:56 AM

Sorting basically works on columns, not rows. I would approach your situation with a macro. Are you able to use / interested in a VBA solution?

**ript73** · 09-06-2009, 02:02 AM

Thanks for the reply. Actually, I'm using openoffice, so not sure what else would work for that.

Thanks!

-Mark

**JBeaucaire** · 09-06-2009, 02:42 AM

I don't use OpenOffice. However, a simple Excel macro to utilize an empty column AA to do sorting for you would look like this:

Sub HorizontalSort()
'JBeaucaire (9/5/2009)
Dim LR As Long, LC As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LR
        LC = Range("A1").End(xlToRight).Column
        Range(Cells(i, "A"), Cells(i, LC)).Copy
        Range("AA1").PasteSpecial xlPasteAll, Transpose:=True
        Columns("AA:AA").Sort Key1:=Range("AA1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        Range(Range("AA1"), Range("AA1").End(xlDown)).Copy
        
        Cells(i, "A").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Columns("AA:AA").ClearContents
    Next i

Application.ScreenUpdating = True
End Sub

This web site appears to offer an OpenOffice Basic converter for Excel VBA:
http://www.business-spreadsheets.com/vba2oo.asp

You can run the code through there to get a possible OpenOffice "version" of my macro.