VBA sort each dataset without removing blank rows

[komhs]

Hi to all and thanks in advance for the help...
I am trying to create a macro which will do the following in my sheet: I want to sort (each time with a column of my choice), each one of the small data sets, which are located between empty lines of a large data set.
For example:
I want to sort by column E (smallest to larger) the entire Data Set from A2:I16, but I want sorting to be made individually for each one of the following data sets (A3:I6, A8:I11, A14:I17) and by keeping exactly where they are the blank rows (in this example 2, 7, 12, 18) as well.
As you can see in the second picture, the data set has been set in alphabetical order in each one of the -between the blank rows- data sets.
Any code (not necessarily exclusively based on this example, because I have different datasets in different sheets) would be helpful
Thanks again

 

[Akuini]

@komhs
Try this:

Sub a1185638a()
Dim i As Long
Application.ScreenUpdating = True
ActiveSheet.AutoFilterMode = False
With ActiveSheet.UsedRange.Resize(, 9)
    .AutoFilter Field:=5, Criteria1:="*"
    With .SpecialCells(xlCellTypeVisible)
        For i = 2 To .Areas.Count
'        Debug.Print .Areas(i).Address
        .Areas(i).Sort Key1:=.Areas(i).Cells(1, 5), Order1:=xlAscending, Header:=xlNo
        Next
    End With
End With
Application.ScreenUpdating = True
End Sub

 

[komhs]

@komhs
Try this:

Sub a1185638a()
Dim i As Long
Application.ScreenUpdating = True
ActiveSheet.AutoFilterMode = False
With ActiveSheet.UsedRange.Resize(, 9)
    .AutoFilter Field:=5, Criteria1:="*"
    With .SpecialCells(xlCellTypeVisible)
        For i = 2 To .Areas.Count
'        Debug.Print .Areas(i).Address
        .Areas(i).Sort Key1:=.Areas(i).Cells(1, 5), Order1:=xlAscending, Header:=xlNo
        Next
    End With
End With
Application.ScreenUpdating = True
End Sub

So. when I want to change the column, I change the number in Field and the number in cells accordingly? Which means that if I want to sort by Column 9, I will do as in the picture?

Thanks in advance

 

[Akuini]

First of all, I made a mistake, actually Criteria1:="*" should be Criteria1:="<>" and also I forgot to reset the autofilter. So use this one instead.

Sub a1185638c()
Dim i As Long
Application.ScreenUpdating = True
ActiveSheet.AutoFilterMode = False
With ActiveSheet.UsedRange.Resize(, 9)
    .AutoFilter Field:=5, Criteria1:="<>"
    With .SpecialCells(xlCellTypeVisible)
        For i = 2 To .Areas.Count
'        Debug.Print .Areas(i).Address
        .Areas(i).Sort Key1:=.Areas(i).Cells(1, 5), Order1:=xlAscending, Header:=xlNo
        Next
    End With
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

So. when I want to change the column, I change the number in Field and the number in cells accordingly? Which means that if I want to sort by Column 9, I will do as in the picture?

Yes. But you don't need to change this part Field:=5, base on your example, you can use Field 1 to 9, the result are the same.

 

[komhs]

Thank you so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.