Last one for the day! Auto sort.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

loc - 22 May 2007 17:00 GMT

Can you auto sort a group of cells when a value is changed?

Reply to this Message

Gord Dibben - 22 May 2007 17:27 GMT

Yes.

See this google search result for Formula method or VBA method.

http://snipurl.com/1lkb6

Gord Dibben MS Excel MVP

>Can you auto sort a group of cells when a value is changed?

Reply to this Message

loc - 22 May 2007 17:57 GMT

i bit confused.....i just want it to sort when something is changed...i dont
want to have to hit a alt f8 plus run.....

> Yes.
>
[quoted text clipped - 5 lines]
>
> >Can you auto sort a group of cells when a value is changed?

Reply to this Message

Gord Dibben - 22 May 2007 19:22 GMT

I guess you missed the event code that Sandy posted.

In the sheet module for that sheet, copy and paste this macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim EndData As Long
If Target.Column <> 2 Then Exit Sub
Application.ScreenUpdating = False
EndData = Cells(Rows.Count, 1).End(xlUp).Row
With Range(Cells(2, 1), Cells(EndData, 2))
.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
Application.ScreenUpdating = False
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.

The event code operates on column B(2) as written.

Gord

>i bit confused.....i just want it to sort when something is changed...i dont
>want to have to hit a alt f8 plus run.....
[quoted text clipped - 8 lines]
>>
>> >Can you auto sort a group of cells when a value is changed?

Reply to this Message