select cell after sorting

S

Steve

Private Sub Worksheet_Change(ByVal Target As Range)

Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("a1").Select



End Sub

Easy enough. I wanted the spread sheet to sort the column by ascending once
the user enters new data. I also want the new data to be the active selection
after it has been sorted. Does anyone know how I can do this? Thanks.
 
B

Bernie Deitrick

Steve,

As long as the new value is unique: otherwise, this will pick up the first instance.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myVal As Variant
myVal = Target.Value

Columns("A:A").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A:A").Find(myVal).Select

End Sub

Of course, you can get around that limitation, but it requires more work....

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top