VBA Auto Sort A-Z

[jamescooper]

Hello this code sorts B automatically from B2 downwards when a change is made.

How would I alter the code so that it only sorts B3 and down, so B1 the title of the column and B2 are constant. Tried a few alterations to the below without any success.

Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)


On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B1").Sort Key1:=Range("B2"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


End If


End Sub

 

[Eric W]

Change this line:

Range("B1").Sort Key1:=Range("B2"), _

to

Range("B[COLOR=#ff0000]2[/COLOR]").Sort Key1:=Range("B[COLOR=#ff0000]3[/COLOR]"), _

 

[jamescooper]

Change this line:

Range("B1").Sort Key1:=Range("B2"), _

to

Range("B[COLOR=#ff0000]2[/COLOR]").Sort Key1:=Range("B[COLOR=#ff0000]3[/COLOR]"), _

Thanks Eric W.

Doesn't quite work, it is still sorting B2:B

Private Sub Worksheet_Change(ByVal Target As Range)


On Error Resume Next
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("B2").Sort Key1:=Range("B3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


End If


End Sub

 

[Eric W]

Sorry, I should have tested better. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Range("B3:B1000").Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlNo
    End If

End Sub

I don't know if you need any of the other miscellaneous parameters on the Sort, but they are usually not needed.

 

[jamescooper]

Sorry, I should have tested better. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Range("B3:B1000").Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlNo
    End If

End Sub

I don't know if you need any of the other miscellaneous parameters on the Sort, but they are usually not needed.

Thank you, this works nicely.