Sorting a range in ascending order in a spreadsheet containing headings across multiple rows

[vbabeginnerjae]

Hi, I am new to VBA and been attempting to develop a macro that will sort data for trays for theatre supplies (in ascending order) by Dispatch Date and Time. Each particular item has it's own heading, followed by Unit, Serial no., Dispatch Time. I have managed to get my code to loop through and select each "Unit" and then offset to the start of the data range and select columns B:I and sorting on column I (Dispatch Time). The challenges I am having is that not all the data is sorting correctly on Dispatch Time (some entries seems to get missed in the sort - Dispatch Time is a Date / Time field). Also when selecting the range for rows where the row is either blank or only comprised of one row the sort affects data in the next section. I'm not sure how to get the macro to just sort for rows in the range where the row count is >1, otherwise skip and go to next "Unit". The format columns A to L should remain consistent for data that is exported from our system, but the number of rows in the spreadsheet will vary. Thanks, any help with this would really be appreciated! Below is a copy of my macro:

Public Sub SortUnitsByDateTimeAsc()
MsgBox "Sort ascending on DISPATCH TIME in progress"

Dim DataRange As Range
Dim cell As Range
Set DataRange = Range("C:C")

For Each cell In DataRange.Cells
If (cell.Value = "Unit") Then
cell.Select

ActiveCell.Offset(1, -1).Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select

ActiveCell.Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Sort Key1:=ActiveCell.Offset(0, 7).Range("A1"), Order1:= _
xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
Selection.End(xlDown).Select
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 1).Range("A1").Select

End If

Next
Application.Goto Reference:="R1C1"
MsgBox "Sort ascending on DISPATCH TIME complete"
End Sub

 

[Oeldere]

It would be nice if you told us, you posted the same question also on another forum.

See the link below.

Sorting a range in ascending order in a spreadsheet containing headings multiple rows

 

[vbabeginnerjae]

Sorry Oeldere. Someone was kind enough to assist me with my problem on another site and the link to the solution is below:
Sorting a range for a varying number of rows in ascending order in a spreadsheet containing headings across multiple rows - Excel User Group