Auto Sort by 2 columns VBA Question

[Juddman79]

Apologies if this question have been answered elsewhere. I'm a complete novice when it comes to VBA Code but I've been trying to sort data alphabetically (Column A) which I achieved with the below code.

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Range("A1").Sort Key1:=Range("A3"), _
          Order1:=xlAscending, Header:=xlGuess, _
          OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
    End If
End Sub

The data range of the sheet is A3 to W100 (Rows 1 and 2 are headers). Is it possible to automatically sort by Column A (Location - alphabetically) first and then to sort the data by Column F (Arrival Date - Ascending), but only when both of these cells are populated with data?

I did have a second question but that issue seems to have rectified itself.

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:A, F:F")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Range("A" & Target.Row) <> "" And Range("F" & Target.Row) <> "" Then
      Range("A2:F" & Range("A" & Rows.Count).End(3).Row).Sort _
        key1:=Range("A2"), order1:=xlAscending, key2:=Range("F2"), order2:=xlAscending, Header:=xlYes
    End If
  End If
End Sub

 

[Juddman79]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:A, F:F")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Range("A" & Target.Row) <> "" And Range("F" & Target.Row) <> "" Then
      Range("A2:F" & Range("A" & Rows.Count).End(3).Row).Sort _
        key1:=Range("A2"), order1:=xlAscending, key2:=Range("F2"), order2:=xlAscending, Header:=xlYes
    End If
  End If
End Sub

That works great thanks for quick assistance. How do I maintain the integrity of the rest of the table so that all the data for that row moves when it is sorted

Also, is it possible to make it so that just the row that has data in column A and F is sorted at that point?? So if I did row 4 that would sort by column A and then if I did row 7 that would then sort etc etc.

I spent hours fiddling around trying to create something without any luck. You guys are brilliant on this forum.

 

[Juddman79]

To maintain the integrity of the rest of the table so that all the data for that row moves when it is sorted do I just change Row 5 of the code to this

Range("A2:X101" & Range("A" & Rows.Count).End(3).Row).Sort

It seems to work but just wanted to check.

Thanks in advance

 

[DanteAmor]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A:A, F:F")) Is Nothing Then
    If Target.CountLarge > 1 Then Exit Sub
    If Range("A" & Target.Row) <> "" And Range("F" & Target.Row) <> "" Then
      Range("A2:X" & Range("A" & Rows.Count).End(3).Row).Sort _
        key1:=Range("A2"), order1:=xlAscending, key2:=Range("F2"), order2:=xlAscending, Header:=xlYes
    End If
  End If
End Sub