Automatically Sort Column with If Then Statement in VBA

Question

I am trying to sort column A in ascending order if a cell in column J or K is changed. The tricky part is that I want the code to look at column K first. If nothing is changed in that column, then look at column J for a change, which will always happen. The reason I want to do this is that I want the code to wait to run the sort function until after all information has been entered into that row first so that it isn't sorting too soon and I have to go looking for the row to continue to enter more data. Column K, however, doesn't always have a change like column J does. In that case, it can sort after I've changed the content in column J; otherwise, sort after entering data into column K. I hope this makes sense. Below is the code I thought would work, but it doesn't. After more thought, I realized that it probably doesn't work because it looks at column K first, but if after looking at column K and it didn't change, no new data is entered into column J to trigger the sort function. Am I right in this thought process? I don't know how else to code this, though.

Private Sub Worksheet_Change(ByVal Target As Range)

 On Error Resume Next 

If Not Intersect(Target, Range("k:k")) Is Nothing Then 

    Range("a1").Sort Key1:=Range("a2"), \_ 

    Order1:=xlAscending, Header:=xlYes, \_ 

    OrderCustom:=1, MatchCase:=False, \_ 

    Orientation:=xlTopToBottom 

ElseIf Not Intersect(Target, Range("j:j")) Is Nothing Then 

    Range("a1").Sort Key1:=Range("a2"), \_ 

    Order1:=xlAscending, Header:=xlYes, \_ 

    OrderCustom:=1, MatchCase:=False, \_ 

    Orientation:=xlTopToBottom

End If 

End Sub

Answer 1

Hi Michelle,

try:

Private Sub Worksheet_Change(ByVal Target As Range) 

If Intersect(Target, Range("J:K")) Is Nothing Or _ 

      Target.Count > 1 Then Exit Sub 

If Application.CountA(Range(Cells(Target.Row, "A"), Cells(Target.Row, "K"))) = 11 Then 

      ActiveSheet.UsedRange.Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes 

End If 

End Sub

Claus

Answer 2

I couldn't tell you what's wrong with it. It just doesn't work. It does nothing when I change either cell.

Answer 3

If that is what you want, the code that you posted should work. What is the problem with it?

Answer 4

There are no formulas in column A, but I like to keep column A sorted alphabetically. I just don't want it to sort until all cells in a row have been entered. The row moving elsewhere is not disconcerting at all. I expect it to do so. It works great in the other worksheets where I don't need an ElseIf. I'm unfamiliar with Worksheet_Activate. Is that automatic? If not, then I might as well just manually sort.

Answer 5

Does column A contain formulas whose result depends on columns J and K?

If not, it doesn't seem very useful to sort on column A when you enter or edit data in columns J or K.

Apart from that, I am not in favor of sorting a sheet 'live' - it can be disconcerting if the row you just edited moves elsewhere.

If I want a sheet to be sorted automatically, I prefer to do so in the Workbook_Open and Worksheet_Activate event procedures; that is less jarring.