Automatic Sorting?

[Ted]

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,

 

[Otto Moehrbach]

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]

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
=========

 

[Roger Govier]

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 data>View Code
Paste code into white pane that appears
Alt + F11 to return to Excel
--
Regards
Roger Govier

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
=========



__________ 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

 

[Gord Dibben]

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

 

[Otto Moehrbach]

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

 

[Otto Moehrbach]

Gord
That's a good point to keep in mind. Thanks. Otto