Sorting rows

[Guest]

Hello, I hope you can provide me a solution to this problem. I have a series
of data as outlined below. The data list has over 4000 rows and I need to
sort each row in ascending order, while keeping the data association in
column A.


A B C D E F G

Date B1 B2 B3 B4 B5 B6
1 5/7/1988 3002 4419 1729 4934 4267 1507
2 5/14/1988 1885 1507 2366 1973 1154 2237
3 5/21/1988 1507 4093 812 4770 2563 2237
4 5/28/1988 3657 1227 1973 1372 2089 4841

When I am finished sorting I would like to be able to see the data :

1 5/7/1988 1507 1729 3002 4267 4419 4934
2 5/14/1988 1154 1507 1885 1973 2237 2366
3 5/21/1988 812 1507 2237 2563 4093 4770
4 5/28/1988 1227 1372 1973 2089 3657 4841

I have tried several methods but I have not found anything that really works
well. The best I have done is converting the data using data list but that
really does not help me.

 

[merjet]

Copy | Paste Special | Transpose the numbers to another range of
cells. Sort the result, each column independently. Copy | Paste
Special | Transpose the sorted numbers back to their original
location. Since you have 4000 rows (and unless you have Excel 2007),
you will have to do it in chunks. The following Sub should help with
the sorting. Change the number of columns, etc. to suit.

Sub Macro1()
Dim iEnd As Integer
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
iEnd = ws.Range("A1").End(xlDown).Row
For iCol = 1 To 4
ws.Range(Cells(1, iCol), Cells(iEnd, iCol)).Sort _
Key1:=Cells(2,iCol), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Next iCol
End Sub

Hth,
Merjet

 

[strive4peace]

code: SortSelection_LeftToRight_ByRow
---

Hi Lowell,

At first, I thought you would have to transpose too -- so I recorded a
macro and then was modifying the code when I found something out -- you
can specify orientation in the Sort!

so, paste this code into a general module
select the DATA to sort without header rows or columns

I think this is what you are looking for ... if not, it was an
interesting find <smile>

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
Sub SortSelection_LeftToRight_ByRow()

'crystal 5-12-07
'strive4peace

'rearrange the cells in each column
'from the first column of the selection to the last one
'on each row
'in ascending order

Dim mRow1 As Long _
, mRow2 As Long _
, mRow As Long _
, mCol1 As Long _
, mCol2 As Long _
, mCol As Long

'-------- assumes the data to be sorted is selected
mRow1 = Selection.Row
mRow2 = Selection.Rows.Count + mRow1 - 1

mCol1 = Selection.Column
mCol2 = Selection.Columns.Count + mCol1 - 1

'-------------------------

'IF data has a header row and header column
'mRow1 = mRow1 + 1
'mCol1 = mCol1 + 1

For mRow = mRow1 To mRow2

'xlTopToBottom is xlSortRows = 2
'xlSortColumns = 1

Range(Cells(mRow, mCol1), Cells(mRow, mCol2)).Sort _
Key1:=Range(Cells(mRow, mCol1), Cells(mRow, mCol2)) _
, Order1:=xlAscending _
, Header:=xlNo _
, Orientation:=xlSortRows

Next mRow
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[merjet]

Interesting, indeed. Thanks!

Merjet

 

[strive4peace]

you're welcome, Merjet

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks, your terrific, the sub routine worked perfectly.

 

[strive4peace]

you're welcome, Lowell happy to help


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*