Auto Sort by 2 columns VBA Question

Juddman79

New Member
Joined
Feb 17, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA 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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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

New Member
Joined
Feb 17, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Try this:

VBA Code:
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.
 
Last edited:

Juddman79

New Member
Joined
Feb 17, 2020
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

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

It seems to work but just wanted to check.

Thanks in advance
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this:

Rich (BB code):
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,390
Members
412,386
Latest member
Yasaman
Top