Can someone give me a simple macro to sort a column?
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 |
Can someone give me a simple macro to sort a column?
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 |
Can someone give me a simple macro to sort a column?
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 |
Can someone give me a simple macro to sort a column?
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 |
Can someone give me a simple macro to sort a column?
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 |
All times are GMT +1. The time now is 09:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com