Auto sort three separate ranges within the same column

[MTBAha]

Hello,

I found this VBA that auto sorts a Multiple Column Groups Separately.
____________________________________________
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Range("A5:B50").Sort _
Key1:=Range("A5"), Order1:=xlAscending, _
Key2:=Range("B5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

ElseIf Target.Column = 3 Then

Range("C5:D50").Sort _
Key1:=Range("C5"), Order1:=xlAscending, _
Key2:=Range("D5"), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


End If
End Sub
__________________________________________

But I need to auto sort two separate ranges within the same column like in the image below.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Cell A[/TD]
[TD]Cell B[/TD]
[TD]Cell C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product[/TD]
[TD]Order Status[/TD]
[TD]Expiration Date[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Carrot[/TD]
[TD]Pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Orange[/TD]
[TD]Pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pepper[/TD]
[TD]Complete[/TD]
[TD]2018/12/20[/TD]
[/TR]
</tbody>[/TABLE]

Need to be able to auto sort another table located below

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Cell A[/TD]
[TD]Cell B[/TD]
[TD]Cell C[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Product[/TD]
[TD]Order Status[/TD]
[TD]Expiration Date[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]Pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Pear[/TD]
[TD]Complete[/TD]
[TD]2018/10/15[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Onion[/TD]
[TD]Complete[/TD]
[TD]2018/12/25[/TD]
[/TR]
</tbody>[/TABLE]

Please help with VBA code.

Thank you!

 

[footoo]

Try creating a macro via the macro recorder.

 

[MTBAha]

Hi,

Thanks for your response!

I tried it, but it doesn't work. What it does just selects the range of cells (I assume it's the first action of the macros I recorded). Is it at all possible to sort two separate ranges within the same column and the same sheet?

 

[footoo]

The macro recorder records only what you do.
Turn on the recorder, perform all the stuff you want to do, turn off the recorder.

 

[MTBAha]

Hi,

It recorded every step, but when I apply it to the button it only does the first line and stops. Is it possible at all to sort two different ranges within the same column?

I appreciate your response.

 

[Fluff]

If col A is values, not formulas & you have blank rows between sections. Try

Sub SortGroups()
   Dim Rng As Range
   
   For Each Rng In Range("A:A").SpecialCells(xlConstants).Areas
      Rng.CurrentRegion.Sort key1:=Rng.Resize(1, 1), order1:=xlAscending, header:=xlYes, MatchCase:=False
   Next Rng
End Sub

 

[MTBAha]

Thanks a lot for the help! I really appreciate all your responses! Thanks you Fluff!

 

[Fluff]

You're welcome