Macro to sort numeric rows left to right

[Carlton A. Barlow]

I need to create a macro to sort 659 rows by 46 columns. The rows contain
numeric values that need to be sorted for lowest (left) to highest (right).
When I sort manually left to right, the values for every row are not sorted
correctly.

Additionally when I sort manually, the left column in some rows are blank.
This is a spreadsheet that has cells that have been manually updated.

Macro below

Sub SortLefttoRight()
'
' SortLefttoRight Macro
' Macro recorded 12/1/2007 by Carlton A. Barlow
'

'
Range("G15:AZ673").Select
Range("AZ673").Activate
Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End Sub

All help is greatly appreciated

 

[D.]

Range("G15:AZ673").Sort Key1:=Range("G15"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight,
_
DataOption1:=xlSortNormal
End Sub
is column G the primary column?

 

[Carlton A. Barlow]

Column G should be the lowest value when sorting is complete.

 

[Gord Dibben]

This worked on a smaller test range.

Sub SortLefttoRight()
'
' SortLefttoRight Macro
' Macro recorded 12/1/2007 by Carlton A. Barlow
'

'
Range("G15:O31").Select
For Each cell In Range("G15:G31")

Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next
End Sub


Gord Dibben MS Excel MVP

 

[Carlton A. Barlow]

Gord,

I modified your macro to include my range and when I run the macro it just
loops and never finishes. Also none of the cells in the range are sorted
correctly. The modifications I made are below.

Range("G15:AZ673").Select
For Each cell In Range("G15:AZ673")
Selection.Sort Key1:=Range("G15"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next
End Sub

 

[Gord Dibben]

Note the For Each range in my version is one column only.

Select the range to sort G15:AZ673 but the sort key is column G

You must change your For Each range to

For Each cell In Range("G15:G673")


Gord

 

[Carlton A. Barlow]

I'm sure I'm not explaining my request to be helped correctly. Below is an
example range of the data to be sorted

g15 h15 i15 j15 k15 l15
0.1830 0.1759 0.1980 0.1890 0.1884 0.1945
0.0380 0.0385 0.0332 0.0340 0.0349 0.0346
0.0565 0.0920 0.0610

After sorting correctly, the range should appear like below

g15 h15 i15 j15 k15 l15
0.1759 0.1830 0.1884 0.1890 0.1945 0.1980
0.0332 0.0340 0.0346 0.0349 0.0380 0.0385
0.0565 0.0610 0.0920

 

[Gord Dibben]

Your explanation was clear....my testing was not good.

This from Tom Ogilvy will work.

Sub SortRows()
'Tom Ogilvy macro
Dim r As Long
Dim Lrow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row

'Make the r = 1 whatever the first row of data you want to sort on is.
'The Cells(r, 1) means your data starts in Col 1 or Col A - adjust to suit
'The resize(1, 4) expands the range to 1 cell deep by 4 cells wide

For r = 15 To Lrow 'row 15 to last row
With Cells(r, 7).Resize(1, 45) '7 is column G.......45 is column AZ
.Sort key1:=Cells(r, 7), Order1:=xlAscending, Header:=xlGuess, _
Orientation:=xlLeftToRight, DataOption1:=xlSortNormal
End With
Next r

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Gord

 

[Carlton A. Barlow]

Gord,

I appreciate the responses. I'm not clear as to what changes I need to make
for the last macro example to with my spreadsheet.

 

[Gord Dibben]

I tried to set it up with your data range being G15:AZ673

The instructions given by Tom...........

have been used in the code I provided.

Does it not do what you want?


Gord

 

[Gord Dibben]

My assumption was that row 673 in column G would be the last row with data.

If different, post back.


Gord

 

[Carlton A. Barlow]

Gord,

I just applied the macro and it worked perfectly. I misunderstood your
previous message that it was an example, not the actual macro.

Thanks!

 

[Gord Dibben]

Great.

Thanks for the feedback and to Tom for the original.


Gord

 

[Carlton A. Barlow]

I don't the it will exceed 700 rows if your adjust for 700 that would be great.

 

[Gord Dibben]

You don't need adjusting.

The line Lrow = ActiveSheet.Cells(Rows.Count, "G").End(xlUp).Row

adjusts for added rows.


Gord