If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatic Sorting?
I have a worksheet.
Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, |
#2
|
|||
|
|||
Automatic Sorting?
Ted
I don't know what you mean by "automatically", but the following little macro will sort what you want, regardless of how many rows you have. Note that this macro is written for data as you stated, that is, starts in row 3 (ignoring headers), and is in columns A:W. Also note that your data must not have any entries in Column A below your data. The macro, as written, assumes that all data from A3 to the last entry in Column A is to be sorted. HTH Otto Sub SortAll() Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) rColA.Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub "Ted" wrote in message ... I have a worksheet. Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, |
#3
|
|||
|
|||
Automatic Sorting?
Otto;
Thank you for your reply and your help. Your macro seems to work great. Though what I meant by "automatically" was I was hoping to have the sort perform by itself any time the data are revised or changed, without me having to do anything. Regards, -Ted ========= "Otto Moehrbach" wrote in message ... Ted I don't know what you mean by "automatically", but the following little macro will sort what you want, regardless of how many rows you have. Note that this macro is written for data as you stated, that is, starts in row 3 (ignoring headers), and is in columns A:W. Also note that your data must not have any entries in Column A below your data. The macro, as written, assumes that all data from A3 to the last entry in Column A is to be sorted. HTH Otto Sub SortAll() Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) rColA.Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub "Ted" wrote in message ... I have a worksheet. Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, |
#4
|
|||
|
|||
Automatic Sorting?
Hi Ted
Then making use of Otto's code, you could just add this as a worksheet event on your sheet with the data. Any change within any of the data within your sort range, will trigger a re-sort Private Sub Worksheet_Change(ByVal Target As Range) Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) If Target.Count 1 Then Exit Sub If Target.Column 3 Or Target.Column = 6 Then If Not (Intersect(Target, rColA)) Is Nothing Then Application.EnableEvents = False Sortall Application.EnableEvents = True End If End If End Sub To use Copy code above Right click on sheet tab with dataView Code Paste code into white pane that appears Alt + F11 to return to Excel -- Regards Roger Govier "Ted" wrote in message ... Otto; Thank you for your reply and your help. Your macro seems to work great. Though what I meant by "automatically" was I was hoping to have the sort perform by itself any time the data are revised or changed, without me having to do anything. Regards, -Ted ========= "Otto Moehrbach" wrote in message ... Ted I don't know what you mean by "automatically", but the following little macro will sort what you want, regardless of how many rows you have. Note that this macro is written for data as you stated, that is, starts in row 3 (ignoring headers), and is in columns A:W. Also note that your data must not have any entries in Column A below your data. The macro, as written, assumes that all data from A3 to the last entry in Column A is to be sorted. HTH Otto Sub SortAll() Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) rColA.Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub "Ted" wrote in message ... I have a worksheet. Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, __________ Information from ESET Smart Security, version of virus signature database 4758 (20100110) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 4758 (20100110) __________ The message was checked by ESET Smart Security. http://www.eset.com |
#5
|
|||
|
|||
Automatic Sorting?
Ted
I do not recommend auto-sorting upon entry. Once your list gets longer, it is very difficult to find and fix any data entry mistakes. I like to confirm first that correct data has been entered then do the sort. Gord Dibben MS Excel MVP On Sun, 10 Jan 2010 10:58:51 -0600, "Ted" wrote: Otto; Thank you for your reply and your help. Your macro seems to work great. Though what I meant by "automatically" was I was hoping to have the sort perform by itself any time the data are revised or changed, without me having to do anything. Regards, -Ted ========= "Otto Moehrbach" wrote in message ... Ted I don't know what you mean by "automatically", but the following little macro will sort what you want, regardless of how many rows you have. Note that this macro is written for data as you stated, that is, starts in row 3 (ignoring headers), and is in columns A:W. Also note that your data must not have any entries in Column A below your data. The macro, as written, assumes that all data from A3 to the last entry in Column A is to be sorted. HTH Otto Sub SortAll() Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) rColA.Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub "Ted" wrote in message ... I have a worksheet. Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, |
#6
|
|||
|
|||
Automatic Sorting?
Ted
VBA can do that too. But be aware that the macro would have to have a trigger (to tell it when to run). I could write the macro to do the sort whenever a change occurs in column A or B or F. That would be the trigger. Since you want to sort by only those 3 columns, any changes in any other columns would not effect the state of the sorted data. If this is what you want, the following macro will do that. Note that this macro is a sheet event macro and MUST be placed in the sheet module of your sheet, not in a regular module. Access that module by right-clicking on the sheet tab and selecting View Code. Paste this macro into that module. "X" out of the module to return to your sheet. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)) If Not Intersect(Target, rColA) Is Nothing Or _ Not Intersect(Target, rColA.Offset(, 1)) Is Nothing Or _ Not Intersect(Target, rColA.Offset(, 5)) Is Nothing Then rColA.Resize(, 23).Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If End Sub "Ted" wrote in message ... Otto; Thank you for your reply and your help. Your macro seems to work great. Though what I meant by "automatically" was I was hoping to have the sort perform by itself any time the data are revised or changed, without me having to do anything. Regards, -Ted ========= "Otto Moehrbach" wrote in message ... Ted I don't know what you mean by "automatically", but the following little macro will sort what you want, regardless of how many rows you have. Note that this macro is written for data as you stated, that is, starts in row 3 (ignoring headers), and is in columns A:W. Also note that your data must not have any entries in Column A below your data. The macro, as written, assumes that all data from A3 to the last entry in Column A is to be sorted. HTH Otto Sub SortAll() Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) rColA.Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub "Ted" wrote in message ... I have a worksheet. Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, |
#7
|
|||
|
|||
Automatic Sorting?
Gord
That's a good point to keep in mind. Thanks. Otto "Gord Dibben" gorddibbATshawDOTca wrote in message ... Ted I do not recommend auto-sorting upon entry. Once your list gets longer, it is very difficult to find and fix any data entry mistakes. I like to confirm first that correct data has been entered then do the sort. Gord Dibben MS Excel MVP On Sun, 10 Jan 2010 10:58:51 -0600, "Ted" wrote: Otto; Thank you for your reply and your help. Your macro seems to work great. Though what I meant by "automatically" was I was hoping to have the sort perform by itself any time the data are revised or changed, without me having to do anything. Regards, -Ted ========= "Otto Moehrbach" wrote in message ... Ted I don't know what you mean by "automatically", but the following little macro will sort what you want, regardless of how many rows you have. Note that this macro is written for data as you stated, that is, starts in row 3 (ignoring headers), and is in columns A:W. Also note that your data must not have any entries in Column A below your data. The macro, as written, assumes that all data from A3 to the last entry in Column A is to be sorted. HTH Otto Sub SortAll() Dim rColA As Range Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp)).Resize(, 23) rColA.Sort Key1:=Range("F3"), Order1:=xlAscending, _ Key2:=Range("B3"), Order2:=xlAscending, _ Key3:=Range("A3"), Order3:=xlAscending, _ Header:=xlNo, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub "Ted" wrote in message ... I have a worksheet. Row 1 contains column headers Row 2 is blank Rows 3 through row 548 contain data in columns A through W First sort condition: Column F, smallest to largest Second sort condition: Column B, smallest to largest Third sort condition: Column A, smallest to largest The number of data rows changes constantly - can range from 300 to 600 (or more) rows. How can I sort the data automatically? Thank you, |
Thread Tools | |
Display Modes | |
|
|