Hi-
I have a whole bunch of data in several columns and I want to set up the
spreadsheet so when I click the column title it sorts the data (keeping all
the rows together still) ascending and then if I click the column title again
it sorts it descending and so on. I want to do this for every column. I
know very little about macro coding and I would really appreciate it if
someone could give me a sample one and instructions as I feel this must be
pretty simple. I tried searching on google but I can't seem to find
anything. I would really apprecriate any help anyone might be able to offer.
Thanks!
-Dan

Dan,

Here is one way. Be aware though that once you enter your code this won't
allow you to change row 1 headings, so get the data set-up first


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:IV1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Orth" wrote in message
...
Hi-
I have a whole bunch of data in several columns and I want to set up the
spreadsheet so when I click the column title it sorts the data (keeping
all
the rows together still) ascending and then if I click the column title
again
it sorts it descending and so on. I want to do this for every column. I
know very little about macro coding and I would really appreciate it if
someone could give me a sample one and instructions as I feel this must be
pretty simple. I tried searching on google but I can't seem to find
anything. I would really apprecriate any help anyone might be able to
offer.
Thanks!
-Dan

Thanks - that is just what I want except is there a way for the rows to stay
together? I want the data in the columns to always line up by row so I
believe I can only have it sorted by one column at a time. Is there anyway
to modify the code to get this? Thanks for your help!
-Dan


"Bob Phillips" wrote in message
...
Dan,

Here is one way. Be aware though that once you enter your code this won't
allow you to change row 1 headings, so get the data set-up first


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:IV1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Orth" wrote in message
...
Hi-
I have a whole bunch of data in several columns and I want to set up the
spreadsheet so when I click the column title it sorts the data (keeping
all
the rows together still) ascending and then if I click the column title
again
it sorts it descending and so on. I want to do this for every column. I
know very little about macro coding and I would really appreciate it if
someone could give me a sample one and instructions as I feel this must
be
pretty simple. I tried searching on google but I can't seem to find
anything. I would really apprecriate any help anyone might be able to
offer.
Thanks!
-Dan

Dan,

Yeah, it's pretty straight-forward

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:IV1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
Me.Cells.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
Me.Cells.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

It's good this isn't it, I like it myself with this modified version.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan" wrote in message
...
Thanks - that is just what I want except is there a way for the rows to
stay
together? I want the data in the columns to always line up by row so I
believe I can only have it sorted by one column at a time. Is there
anyway
to modify the code to get this? Thanks for your help!
-Dan


"Bob Phillips" wrote in message
...
Dan,

Here is one way. Be aware though that once you enter your code this
won't
allow you to change row 1 headings, so get the data set-up first


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:IV1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Orth" wrote in message
...
Hi-
I have a whole bunch of data in several columns and I want to set up
the
spreadsheet so when I click the column title it sorts the data (keeping
all
the rows together still) ascending and then if I click the column title
again
it sorts it descending and so on. I want to do this for every column.
I
know very little about macro coding and I would really appreciate it if
someone could give me a sample one and instructions as I feel this must
be
pretty simple. I tried searching on google but I can't seem to find
anything. I would really apprecriate any help anyone might be able to
offer.
Thanks!
-Dan

Thanks a lot! That is exactly what I was looking for!

-Dan


"Bob Phillips" wrote in message
...
Dan,

Yeah, it's pretty straight-forward

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:IV1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
Me.Cells.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
Me.Cells.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

It's good this isn't it, I like it myself with this modified version.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan" wrote in message
...
Thanks - that is just what I want except is there a way for the rows to
stay
together? I want the data in the columns to always line up by row so I
believe I can only have it sorted by one column at a time. Is there
anyway
to modify the code to get this? Thanks for your help!
-Dan


"Bob Phillips" wrote in message
...
Dan,

Here is one way. Be aware though that once you enter your code this
won't
allow you to change row 1 headings, so get the data set-up first


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:IV1")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name < "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlAscending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
.EntireColumn.Sort key1:=.Offset(1, 0), _
order1:=xlDescending, _
header:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dan Orth" wrote in message
...
Hi-
I have a whole bunch of data in several columns and I want to set up
the
spreadsheet so when I click the column title it sorts the data
(keeping
all
the rows together still) ascending and then if I click the column
title
again
it sorts it descending and so on. I want to do this for every column.
I
know very little about macro coding and I would really appreciate it
if
someone could give me a sample one and instructions as I feel this
must
be
pretty simple. I tried searching on google but I can't seem to find
anything. I would really apprecriate any help anyone might be able to
offer.
Thanks!
-Dan