Auto Sort

  • Thread starter Thread starter Natalie
  • Start date Start date
N

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.
 
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.
 
-----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.
.
Click to expand...
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 Auto_OPen()
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
 
Back
Top