Auto Sort

[Natalie]

Is there a way to perform an "auto" sort for a range of
cells? It is a list that I constantly add onto, but I'd
like it to "re-sort" by a certain column header after I
enter something new. Thank you.

 

[acw]

Natalie

one way would be to use the worksheet modules. On the sheet tab, right click, then select view code. Insert the following code. Whenever you change or add a value into Column A, it will resort ascending, and with a header. Expand to cover the columns required and the key required.

Tony

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Column = 1 Then Range("a:a").Sort Key1:=Range("a1"), order1:=xlAscending, header:=xlYes
End Sub

----- Natalie wrote: -----

Is there a way to perform an "auto" sort for a range of
cells? It is a list that I constantly add onto, but I'd
like it to "re-sort" by a certain column header after I
enter something new. Thank you.

 

[Peter Atherton]

-----Original Message-----
Is there a way to perform an "auto" sort for a range of
cells? It is a list that I constantly add onto, but I'd
like it to "re-sort" by a certain column header after I
enter something new. Thank you.
.

Natalie

This sorts Sheet three on column A when there is a
calculation. I have assumed that You might have a
calculation in the sheet.

If not Prerss F9 the Calculate Key.

Paste this into a Module Save and close the workbook then
reopen and it will work.

Obviously change the sheets and range to suit.

Sub AutPen()
Application.OnCalculate = "MySort"
End Sub

Sub MySort()
Dim rng As Range
Dim lastRow As Long
With Sheets("Sheet3")
lastRow = Application.WorksheetFunction.CountA(Range
("A:A"))
Set rng = Range(Cells(1, 1), Cells(lastRow, 4))
rng.Sort Key1:=Range("A7"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End Sub

Peter