Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
D. D. is offline
external usenet poster
 
Posts: 53
Default Macro to sort numeric rows left to right

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?
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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

"D." wrote:

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?

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?




  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?





  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?






  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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" wrote:

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?







  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

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

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

'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


have been used in the code I provided.

Does it not do what you want?


Gord

On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?










  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

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

If different, post back.


Gord

On Sun, 02 Dec 2007 19:31:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

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

'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


have been used in the code I provided.

Does it not do what you want?


Gord

On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?








  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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" wrote:

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

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

'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


have been used in the code I provided.

Does it not do what you want?


Gord

On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?









  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

Great.

Thanks for the feedback and to Tom for the original.


Gord

On Sun, 2 Dec 2007 20:00:01 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

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

'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


have been used in the code I provided.

Does it not do what you want?


Gord

On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?










  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Macro to sort numeric rows left to right

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

"Gord Dibben" wrote:

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

If different, post back.


Gord

On Sun, 02 Dec 2007 19:31:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

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

'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


have been used in the code I provided.

Does it not do what you want?


Gord

On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?









  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default Macro to sort numeric rows left to right

You don't need adjusting.

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

adjusts for added rows.


Gord

On Sun, 2 Dec 2007 20:30:01 -0800, Carlton A. Barlow
wrote:

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

"Gord Dibben" wrote:

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

If different, post back.


Gord

On Sun, 02 Dec 2007 19:31:08 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

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

'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

have been used in the code I provided.

Does it not do what you want?


Gord

On Sun, 2 Dec 2007 17:05:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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


'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
On Sun, 2 Dec 2007 12:39:06 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 11:15:00 -0800, Carlton A. Barlow
wrote:

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" wrote:

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

On Sun, 2 Dec 2007 06:01:01 -0800, Carlton A. Barlow
wrote:

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

"D." wrote:

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?










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort Left-Right & Up-Down Ben Dummar Excel Worksheet Functions 0 April 4th 07 05:56 PM
How do I create a Macro to sort data and insert blank rows & subto karinmpfa Excel Worksheet Functions 2 April 25th 06 09:57 PM
sorting alpha numeric list by first left digit kaduna New Users to Excel 6 January 14th 06 12:10 AM
Sort Macro to Exclude Blank Rows? ScottPcola Excel Worksheet Functions 1 January 5th 06 08:10 PM
Sort Left to Right David Excel Discussion (Misc queries) 1 February 3rd 05 04:18 PM


All times are GMT +1. The time now is 07:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"