Column Sorting
 

Try recording a macro when you sort your range.

There's an option button on the sort dialog that allows you to specify right to
left.

I got this (in xl2002):

Range("C4:AN4").Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

I'd modify it to something like:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4")

With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End With

End Sub

(you really meant sort by column???)



Todd Huttenstine wrote:

Ive got a worksheet with a table. On row 4 I have
different categorys. What is the code I would use for it
to look in Row 4 starting with column C and sort
alphabetical? My data goes from column C to Column AN in
row 4. I would like to have the code inserted into a
button so I can just click and it sort.

Thank you

Todd Huttenstine

--

Dave Peterson

Column Sorting
 

This almost worked, It sorted the columns but did not move
the data under the columns with the columns when it sorted
the alphabetical. How do you fix this?
-----Original Message-----
Try recording a macro when you sort your range.

There's an option button on the sort dialog that allows
you to specify right to
left.

I got this (in xl2002):

Range("C4:AN4").Select
Selection.Sort Key1:=Range("C4"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

I'd modify it to something like:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4")

With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End With

End Sub

(you really meant sort by column???)



Todd Huttenstine wrote:

Ive got a worksheet with a table. On row 4 I have
different categorys. What is the code I would use for
it
to look in Row 4 starting with column C and sort
alphabetical? My data goes from column C to Column AN
in
row 4. I would like to have the code inserted into a
button so I can just click and it sort.

Thank you

Todd Huttenstine

--

Dave Peterson

.

Column Sorting
 

If your data consists of values (not formulas), then I think that this'll work.
I used a sort routine that was posted by Jim Rech and enhanced by Tom Ogilvy.

You can find the enhanced version in this thread:
http://groups.google.com/groups?thre...40TK2MSFTNGP11

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myArr As Variant
Dim LastRow As Long
Dim wks As Worksheet
Dim dummyRng As Range

Set wks = Worksheets("sheet1")

With wks
Set dummyRng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set myRng = .Range("c4:an" & LastRow)

myArr = Application.Transpose(myRng.Value)
Call QuickSort(myArr, 1, LBound(myArr, 1), UBound(myArr, 1), True)
myRng.Value = Application.Transpose(myArr)
End With

End Sub

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified by T Ogilvy to sort on first column of a two dimensional array
'Modified by T Ogilvy to handle a a second dimension greater than 1 (or zero)
'Modified by T Ogilvy to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) X And i < R)
i = i + 1
Wend
While (X SortArray(j, col) And j L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Todd Huttenstine wrote:

This almost worked, It sorted the columns but did not move
the data under the columns with the columns when it sorted
the alphabetical. How do you fix this?
-----Original Message-----
Try recording a macro when you sort your range.

There's an option button on the sort dialog that allows
you to specify right to
left.

I got this (in xl2002):

Range("C4:AN4").Select
Selection.Sort Key1:=Range("C4"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

I'd modify it to something like:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4")

With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End With

End Sub

(you really meant sort by column???)



Todd Huttenstine wrote:

Ive got a worksheet with a table. On row 4 I have
different categorys. What is the code I would use for
it
to look in Row 4 starting with column C and sort
alphabetical? My data goes from column C to Column AN
in
row 4. I would like to have the code inserted into a
button so I can just click and it sort.

Thank you

Todd Huttenstine

--

Dave Peterson

.


--

Dave Peterson

Column Sorting
 

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4").CurrentRegion

if myrng.row < 4 then _
set myrng = myrng.offset(4-myrng.row)
set myrng = myrng.resize(,38)
With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight
End With

End Sub

Assumes you don't have data below your last row that you don't want sorted
and there are no completely blank rows below row 4 until the end of the
data.

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in message
...
This almost worked, It sorted the columns but did not move
the data under the columns with the columns when it sorted
the alphabetical. How do you fix this?
-----Original Message-----
Try recording a macro when you sort your range.

There's an option button on the sort dialog that allows
you to specify right to
left.

I got this (in xl2002):

Range("C4:AN4").Select
Selection.Sort Key1:=Range("C4"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

I'd modify it to something like:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4")

With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End With

End Sub

(you really meant sort by column???)



Todd Huttenstine wrote:

Ive got a worksheet with a table. On row 4 I have
different categorys. What is the code I would use for
it
to look in Row 4 starting with column C and sort
alphabetical? My data goes from column C to Column AN
in
row 4. I would like to have the code inserted into a
button so I can just click and it sort.

Thank you

Todd Huttenstine

--

Dave Peterson

.

Column Sorting
 

Ouch. That one hurt!

Tom Ogilvy wrote:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4").CurrentRegion

if myrng.row < 4 then _
set myrng = myrng.offset(4-myrng.row)
set myrng = myrng.resize(,38)
With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight
End With

End Sub

Assumes you don't have data below your last row that you don't want sorted
and there are no completely blank rows below row 4 until the end of the
data.

--
Regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
This almost worked, It sorted the columns but did not move
the data under the columns with the columns when it sorted
the alphabetical. How do you fix this?
-----Original Message-----
Try recording a macro when you sort your range.

There's an option button on the sort dialog that allows
you to specify right to
left.

I got this (in xl2002):

Range("C4:AN4").Select
Selection.Sort Key1:=Range("C4"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal

I'd modify it to something like:

Option Explicit
Sub testme01()
Dim myRng As Range

Set myRng = Worksheets("sheet1").Range("c4:AN4")

With myRng
.Sort Key1:=.Cells(1), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlLeftToRight
End With

End Sub

(you really meant sort by column???)



Todd Huttenstine wrote:

Ive got a worksheet with a table. On row 4 I have
different categorys. What is the code I would use for
it
to look in Row 4 starting with column C and sort
alphabetical? My data goes from column C to Column AN
in
row 4. I would like to have the code inserted into a
button so I can just click and it sort.

Thank you

Todd Huttenstine

--

Dave Peterson

.


--

Dave Peterson