How to loop and sort blocks of rows

Requirement:

The user has a number of blocks of rows that are separated by an empty row.

Each block ranges from 2 rows up to 8 rows and there can be hundreds of these blocks all of varying size between 2 and 8.

In Colmn E of the each row the user has a number(could be any number) and wants to use this number to SORT each row out from lowest to highest.

Is there a way the user could loop through all of these blocks sorting each row out as it goes along?

Solution:

I have attached a file that I knocked up for testing the code. The first sheet has a header row and the 2nd sheet no header. Currently all blocks are sorted highest to lowest, change a value in Column E and that block will sort lowest to highest.

I have tweaked the codes a bit too
With header:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Aria, lCol As Long, r As Range, rCel As Range, i As Integer
    
    With Application
        .EnableEvents = 0
        .ScreenUpdating = 0
    End With
    With ActiveSheet
        lCol = .Rows(1).SpecialCells(2).Count
        On Error GoTo ErrHndlr
        For Each Aria In .UsedRange.Columns(1).SpecialCells(2).Areas
            i = i + 1
            For Each rCel In Target
                Set r = Aria.Resize(, lCol)
                If Not Intersect(Target, r.Columns(5)) Is Nothing Then
                    If i = 1 Then
                        r.Sort r.Columns(5), , , , , , , 1
                    Else
                        r.Sort r.Columns(5), , , , , , , 2
                    End If
                End If
            Next
        Next
    End With
    Application.EnableEvents = 1
    Exit Sub
ErrHndlr:
    MsgBox "Unexpected error in the code", 16, "ERROR!"
    Application.EnableEvents = 1
    
End Sub

No header

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Aria, lCol As Long, r As Range, rCel As Range
    
    With Application
        .EnableEvents = 0
        .ScreenUpdating = 0
    End With
    With ActiveSheet
        lCol = .Rows(1).SpecialCells(2).Count
        On Error GoTo ErrHndlr
        For Each Aria In .UsedRange.Columns(1).SpecialCells(2).Areas
            For Each rCel In Target
                Set r = Aria.Resize(, lCol)
                If Not Intersect(Target, r.Columns(5)) Is Nothing Then
                    r.Sort r.Columns(5), , , , , , , 2
                End If
            Next
        Next
    End With
    Application.EnableEvents = 1
    Exit Sub
ErrHndlr:
    MsgBox "Unexpected error in the code", 16, "ERROR!"
    Application.EnableEvents = 1
    
End Sub

Try these codes in your actual file, if you still get an error then attach your file and explain exactly how the data gets updated.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1199910-looping-and-sorting-blocks-of-rows

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.